Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- Percent of total YTD (urgent, please help)

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-07-2016 04:11 AM - edited 07-07-2016 08:32 AM

Hi to all,

I need to put in a report the YTD sales and % of total of dimensions for YTD.

I know that it is not possible to create aggregation of an aggregated measure so I made YTD with the formula below:

IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )

RETURN 'FT_GROSS_AMOUNT'n

ELSE 0

If I put the formula below in an pivot without dimensions the overall sum is OK.

If I put the Sum [_ForAll_] ('Ytd Gross Act'n) for the current year is OK, for all the other is much more than the total of YTD for the year. And if I create % of total for Ytd column it sums to 38% instead of 100%.

Is that a bug?

How can I fix it?

Please help!

Thanks in advance,

Adelaida

PS I discover what Sum [_ForAll_] ('Ytd Gross Act'n) gives as result , it gives the same as Sum [_ForAll_] ('FT_GROSS_AMOUNT'n). But why? It works fine if I make a condition on product dimension or other. Is it something wrong in my formula below?

Accepted Solutions

Solution

07-15-2016
04:38 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Adelaida

07-07-2016 06:53 AM

Hello,

In simpler words,

If you wants to find %of any column, then you can write click on column name (measure) and then click on "Create" and then click on "Percentage of Total".

Let's say column name is PROFIT.

so, you can make a new aggregated column as a Percenatge Total of PROFIT.

Thanks

Regards

Harshil Gandhi

Harshil Gandhi

All Replies

Solution

07-15-2016
04:38 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Adelaida

07-07-2016 06:53 AM

Hello,

In simpler words,

If you wants to find %of any column, then you can write click on column name (measure) and then click on "Create" and then click on "Percentage of Total".

Let's say column name is PROFIT.

so, you can make a new aggregated column as a Percenatge Total of PROFIT.

Thanks

Regards

Harshil Gandhi

Harshil Gandhi

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hargan

07-07-2016 07:24 AM

Hai Hargan,

It works for calculated items, but not for aggregate measures. If you create Year to date column for Profit, you cannot create % of Year to date. That's why my formula.

And yes, I have made % of the column calculate as

IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )

RETURN 'FT_GROSS_AMOUNT'n

ELSE 0

But as you can see from the image attached the result is strange (the total line is 38% instead of 100%). The problem is that VA consider the total equal to Sum [_ForAll_] ('FT_GROSS_AMOUNT'n) instead of Sum [_ForAll_] (IF ( ( 'SALES_DATE'n <= DateFromMDY( Month(DatePart(Now())), DayOfMonth(DatePart(Now())), Year('SALES_DATE'n))) )

RETURN 'FT_GROSS_AMOUNT'n

ELSE 0) so % form total is strange, is buged...

Any idea?

Thanks,

Adelaida

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Adelaida

07-08-2016 07:01 AM

Hello,

Can you please share your dataset and objective of your task if it's not confidential ?

In any case you can mail me i.e (hargan.gandhi@gmail.com)

Thanks

Regards

Harshil Gandhi

Harshil Gandhi

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to hargan

07-15-2016 04:45 AM

Hi Hargan, I have solved it temporary using your hit. I hope they won't need a graphic of the YTD % of total, because there I must use Sum [_ByGroup_] Ytd / Sum [_ForAll_] Ytd and it's bugged. Till then I consider the problem solved. Thank you, Adelaida

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Adelaida

07-15-2016 04:52 AM

Sure.

If you need let me know, will find any solution for the same.

Thanks

Regards

Harshil Gandhi

Harshil Gandhi

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Adelaida

07-15-2016 04:30 AM

Just some thoughts here: What goes wrong when using the supplied functionality for calculating YTD aggregated measure? You can do some calculations on aggregated measures like

Sum [_ByGroup_] aggregated measure / Sum [_ForAll_] aggregated measure