Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaldubai
Creator
Creator

variable evaluation in set analysis

hey all

I have created a variable as follows

vMax_Period_ID

max({<MonthYR={"<=$(=max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID) " no equal sign prior to formula"

now im trying to use this variable in a set analysis and this variable should return one value for each item

my set analysis

=only({<PAC_PERIOD_ID={"$(=$(vMax_Period_ID))"}>}ITEM_COST)


yet the variable doesn't evaluate properly


if we replace the variable with a hard coded value it works just fine

variable.PNG

1 Solution

Accepted Solutions
sunny_talwar

I think I understand your issue.... if you look at the image you posted, only where variable is 60022, set analysis column has a value.

Capture.PNG

The problem is that you are expecting set analysis to be evaluated for each dimension, but it only evaluates per chart. So in your case the Max({<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID) = 60022 which is used across all your dimensions. In order to resolve this issue.... you can use Aggr() function instead of using set analysis.

Aggr(If(PAC_PERIOD_ID = Max(TOTAL <ORGANIZATION_ID, item_code> {<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID), ITEM_COST), ORGANIZATION_ID, item_code, PAC_PERIOD_ID)

View solution in original post

8 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

Try

Aggr(=only({<PAC_PERIOD_ID={"$(=$(vMax_Period_ID))"}>}ITEM_COST), ITEM_COST)

tresesco
MVP
MVP

Or, may be even without aggr(), like:

only({<PAC_PERIOD_ID={"$(=$(vMax_Period_ID))"}>}ITEM_COST)

kaldubai
Creator
Creator
Author

Thank you guys but your responses have not changed anything

sunny_talwar

What if you replace the variable's double quotes to single quote

Max({<MonthYR={'<=$(=Max(AWESOME_IDEA_2))'}>}PAC_PERIOD_ID)

kaldubai
Creator
Creator
Author

the variable actually evaluates correctly but not when inserted  into set analysis

if you may see my above picture ,the variable column actually returns corrects values

im assuming that the variable is not evaluated properly in the set analysis could be syntax 'single or double quotation, dollar sign expansion '

sunny_talwar

I think I understand your issue.... if you look at the image you posted, only where variable is 60022, set analysis column has a value.

Capture.PNG

The problem is that you are expecting set analysis to be evaluated for each dimension, but it only evaluates per chart. So in your case the Max({<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID) = 60022 which is used across all your dimensions. In order to resolve this issue.... you can use Aggr() function instead of using set analysis.

Aggr(If(PAC_PERIOD_ID = Max(TOTAL <ORGANIZATION_ID, item_code> {<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID), ITEM_COST), ORGANIZATION_ID, item_code, PAC_PERIOD_ID)

kaldubai
Creator
Creator
Author

Thank you so you Much Sunny,

your answer is correct and it is giving me correct values yet Im missing the logic you used there ,what total has to do here ???

sunny_talwar

Although your table has only two dimensions, I have using three dimensions in the Aggr() function. The reason I had to use PAC_PERIOD_ID as a dimension is because I am using that in the If statement non aggregated which requires me to add it as a dimension.

Aggr(If(PAC_PERIOD_ID = Max(TOTAL <ORGANIZATION_ID, item_code> {<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID)

Now to get a single value (Max based on ORGANIZATION_ID and item_code), I had to use total key word with both the dimension.....

An easy way to see this would be to do like this Create a new straight table with three dimensions

ORGANIZATION_ID

item_code

PAC_PERIOD_ID

and try these expressions

1) PAC_PERIOD_ID

2) Max(TOTAL <ORGANIZATION_ID, item_code> {<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID)

3) If(PAC_PERIOD_ID = Max(TOTAL <ORGANIZATION_ID, item_code> {<MonthYR={"<=$(=Max(AWESOME_IDEA_2))"}>}PAC_PERIOD_ID), ITEM_COST)

Once you do this, you will sort of understand what I am doing here