Pass values inside pick(match(..)) and loop through the expression each time for a different value

[tas_taba1]

Hello Qlikkers,

This is a follow up question for the earlier post- https://community.qlik.com/t5/Qlik-Sense-App-Development/Include-all-the-dimension-values-regardless....

Right now I have a table in my app as such-

table

where the dimension value is- ValueList('African American','Asian','Hispanic','White') and the expression used as measure is- 

PICK(MATCH(ValueList('African American','Asian','Hispanic','White'),'African American','Asian','Hispanic','White'),
sqrt(Sum({<Race={'African American'}>}ALL Aggr(
Sqr(Sum({<Race={'African American'}>}UtilityWt)-(Sum({<Race={'African American'}>}TOTAL <CinemaStratum> UtilityWt)/Count({<Race={'African American'}>}DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count({<Race={'African American'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count({<Race={'African American'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)-1))
, CinemaPSU, CinemaStratum))),
sqrt(Sum({<Race={'Asian'}>}ALL Aggr(
Sqr(Sum({<Race={'Asian'}>}UtilityWt)-(Sum({<Race={'Asian'}>}TOTAL <CinemaStratum> UtilityWt)/Count({<Race={'Asian'}>}DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count({<Race={'Asian'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count({<Race={'Asian'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)-1))
, CinemaPSU, CinemaStratum))),
sqrt(Sum({<Race={'Hispanic'}>}ALL Aggr(
Sqr(Sum({<Race={'Hispanic'}>}UtilityWt)-(Sum({<Race={'Hispanic'}>}TOTAL <CinemaStratum> UtilityWt)/Count({<Race={'Hispanic'}>}DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count({<Race={'Hispanic'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count({<Race={'Hispanic'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)-1))
, CinemaPSU, CinemaStratum))),
sqrt(Sum({<Race={'White'}>}ALL Aggr(
Sqr(Sum({<Race={'White'}>}UtilityWt)-(Sum({<Race={'White'}>}TOTAL <CinemaStratum> UtilityWt)/Count({<Race={'White'}>}DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count({<Race={'White'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count({<Race={'White'}>}DISTINCT ALL <CinemaStratum> CinemaPSU)-1))
, CinemaPSU, CinemaStratum)))
)

Now, my question is- how can I pass these field values in the set expressions (e.g. African American, Asian, Hispanic, White) dynamically instead of hard coding them for each row. I have thought of variable input but again I don't want to have the variable list input options to be hard coded. Is there any way that I could extract the Race field values and store them in a list and calculate the SE measure for each of the values stored in that list?

As I am exploring different options, I have created a variable called vList- =chr(39) & Concat( Distinct Race , chr(39)& ',' & chr(39)) & chr(39) which is creating a list of all the race values-  {'African American', 'Asian', 'Hispanic', 'White'} and also created the dimension- Valuelist(vList) but I have no idea of how to pass these values into the set expressions for SE calculation.

Any help or suggestions will really help me out! I am attaching the dataset and QS application for reference. Thank you.

[rwunderlich]

You cannot vary the set expression by row.  The set is evaluated only once for the entire chart.  What you are currently doing is calculating all the sets and selecting a different one for each row. 

-Rob

[tas_taba1]

Hello @rwunderlich , thank you for sharing your insights. I understand that the set analysis is done once per chart. But my actual dataset contains approximately 300000 rows many dimensions. As such, it is quite impractical to hard code the row by row calculations. Therefore, I wanted some expert suggestions as to how can I make this dynamic. For example, if I could save all the values of a dimension in variable and run the calculations for one element of the variable at a time. Something like array and loops in any other language.

Any suggestions will be greatly welcomed. Thank you.

[Oleg_Troyansky]

Hi again,

I think someone had sent you "down a rabbit hole" by suggesting the use of ValueList() for this purpose. This solution is not only overly complicated, but it also contains a number of fundamental issues that don't ever work:

- Set Analysis cannot be sensitive to the values of your Dimensions, because it's only evaluated once

- The AGGR function doesn't play well in combination with the ValueList() and they require some special "treatment" if they have to be used together

- The dimensions of the AGGR should be more granular than the dimensions of the chart (my "Third Law of AGGR", as I mentioned in my response to your earlier question).

I think you should get the expected results by simply adding Race to the list of the AGGR Dimensions, but you should test this idea. Perhaps the specifics of your particular calculation may also require some tweaking with Set Analysis.

Let me invite you again to my virtual session on Set Analysis and AGGR, where I'm actually describing all the issues listed above, and a lot more. Based on the nature of your analysis, you will learn from my session more than many other people. Follow the link in my signature below.