## Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Solved
Occasional Contributor
Posts: 6

[ Edited ]

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?

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
SAS Employee
Posts: 11

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

All Replies
Solution
‎07-15-2016 04:38 AM
SAS Employee
Posts: 11

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
Occasional Contributor
Posts: 6

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,

SAS Employee
Posts: 11

Hello,

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

Thanks

Regards
Harshil Gandhi
Occasional Contributor
Posts: 6

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
SAS Employee
Posts: 11

Sure.

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

Thanks

Regards
Harshil Gandhi
Contributor
Posts: 55

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

☑ This topic is solved.