topic Re: Total issue in set analysis in App Development

Total issue in set analysis

dandaanilreddy — Mon, 02 May 2022 14:50:07 GMT

Hello All,

I have used total in set analysis for one of the calculation but its not giving me the correct result. I have month data for each year starting from 2018 to 2026 and i created year data by summing up 12 months for each year. When i used total in set analysis i'm getting 1.05 instead of 1.15 for the below for 2018 year but when i select 2018 its giving 1.15 value but user don't want to select any year. Please can someone let me know how can i get the correct result without selecting year filter? Thanks in advance.

A= C/B

B= 88.62

C= 7727.45

Re: Total issue in set analysis

Or — Mon, 02 May 2022 20:26:04 GMT

Unfortunately, the information you have provided is not sufficient to try and help you with your problem. If you attach some of the actual underlying data and the actual formulas used, it is more likely that someone would be able to help.

Re: Total issue in set analysis

dandaanilreddy — Mon, 02 May 2022 21:09:20 GMT

Thanks for the response. Please find the sample data.

Test:

load * inline

[

Dim, Value , Period

A, 10.08, 2018

A, 15.03, 2019

B, 7727.45, 2018

B, 8482.66, 2019

C, 88.62, 2018

C, 100.05, 2019

];

Pivot table expression: if(dim ='A', (sum(total{<Dim={'C'}>}Value)/sum(total{<Dim={'B'}>}Value))*100,

sum(Value))

When i don't make selections on period its giving me same value for both 2018 and 2019 for Dim = A 1.16 but i am expecting 1.15 for 2018 and 1.18 for 2019 for Dim =A without selecting period filter.

Thanks

Re: Total issue in set analysis

vinieme12 — Tue, 03 May 2022 07:42:30 GMT

This is because you are aggregating on a Dummy/Non-Associated dimension in your chart dim='A'

The total keyword will aggregate it for dim=A(not for Dim=B or Dim=C), which is unrelated so your numerator and denominator for any period will be same

Just remove the total keyword or use a dim which is associated

=if(dim ='A', (sum({<Dim={'C'}>}Value)/sum({<Dim={'B'}>}Value))*100,sum(Value))

Re: Total issue in set analysis

dandaanilreddy — Tue, 03 May 2022 13:06:22 GMT

i have used the period in my set analysis expression and it worked

if(dim ='A', (sum(total<period>{<Dim={'C'}>}Value)/sum(total<period>{<Dim={'B'}>}Value))*100,

sum(Value))