Turn on suggestions

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

Showing results for

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

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-07-2016 04:11 AM
(2422 views)

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?

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.