Total issue in set analysis

[dandaanilreddy]

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

[Or]

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.

[dandaanilreddy]

Hi 

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

[vinieme12]

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))

[dandaanilreddy · Accepted solution]

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))