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

Percent of total YTD (urgent, please help)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Percent of total YTD (urgent, please help)

[ 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?

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?


% of total for YTD.png

Accepted Solutions
Solution
‎07-15-2016 04:38 AM
SAS Employee
Posts: 11

Re: Percent of YTD (urgent, please help)

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


Untitled.png
Regards
Harshil Gandhi

View solution in original post


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

Re: Percent of YTD (urgent, please help)

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


Untitled.png
Regards
Harshil Gandhi
Occasional Contributor
Posts: 6

Re: Percent of YTD (urgent, please help)

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

SAS Employee
Posts: 11

Re: Percent of total YTD (urgent, please help)

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

Re: Percent of total YTD (urgent, please help)

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

Re: Percent of total YTD (urgent, please help)

Sure.

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

 

Thanks

 

Regards
Harshil Gandhi
Contributor
Posts: 53

Re: Percent of total YTD (urgent, please help)

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 452 views
  • 1 like
  • 3 in conversation