Include all the dimension values regardless of user selection of another dimension

[tas_taba1]

Hello Qlikkers,

I have stumbled upon a problem and was hoping if someone could help me out. I have a dataset with the following fields-

datasetI use the fields- CinemaStratum, CinemaPSU and UtilityWt to calculate the measure Standard Error. And the expression I use for this is-

'sqrt(Sum(ALL Aggr(

Sqr(Sum(UtilityWt)-(Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1))

, CinemaPSU, CinemaStratum)))'

It is giving me the correct SE for the entire dataset-

output1

Also, when I use a filter pane for another dimension e.g. Race, it is also showing the correct SE for the particular selection-

output2

But when I create a separate table with Race as the dimension and SE as measure, it is no longer giving the correct row by row SE for each race values-

wrong_result

Because now it is no longer taking into account some of the stratums/PSUs whose value does not exist for the particular race type. But I want all the stratums/PSU's to be included and their corresponding values to be considered as '0' in the respective SE calculation. The desired table is below-

desired_result

I am attaching the app and excel file (for SE calculation demo) for reference. If anyone can give any insight or share their valuable insights, it would be great! Thank you.

[Taoufiq_Zarra · Accepted solution]

@tas_taba1 

I really don't fully understand the calculations , but the suggestion I can make is to use valuelist and Set Analysis.

I don't think you have a many Race, so I get the output:

PFA the qvf file

[tas_taba1]

Hi @Taoufiq_Zarra, thank you for your response. It works like charm for this dataset.

Having said that, the real dataset I am working on is far more complex and vast.So, I have to be able to create a dynamic valuelist() where  users can select the dimensions at will, e.g., in this case if I want to select the AgeYrs field instead of Race as table dimension and still want to see the row by row SE calculations or if I want to create a pivot table with Race as the 1st row value and then have gender as the second row value and so on. Basically I want the solution for multi-dimensional row-level calculations in tables/pivot tables that are also responsive to filters.

But your solution definitely led me to the right direction. May be passing variables as parameters to the valuelist() function is a reasonable option here? I will keep exploring. Thank you!

[Oleg_Troyansky]

Hi,

I didn't have a chance to dive deep into the logic, but from a quick look at your formula I have a feeling that the key to your problem is in a particular rule about using AGGR in a chart, that I am calling in my book "The Third Law of AGGR". Henric Cronström calls the same issue the "grain mismatch".

In short, the dimensions of your AGGR function have to be more granular than the dimensions of your chart. Using your AGGR formula in a chart by Race violates "The Third Law of AGGR", or creates a "grain mismatch", hence producing the wrong results.

You can learn this and a lot more about AGGR from my Masters Summit lecture about AGGR and Set Analysis, that I will be delivering virtually on November 18th. I am inviting you and everyone who is interested in the topic to check out our web site. See the link below.