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: 
eleni_theodorid
Partner - Creator
Partner - Creator

Set Analysis with If()

Dear All,

I have a problem with a Set Analysis. Actually, a want in some cases to get the results of one Set Analysis ELSE get the result of another. Cause is a little bit more complicated (at least in my head right now!), I attached a sample. I want to get the results in pivot table.

Thanks in advanced,

Helen

8 Replies
vgutkovsky
Master II
Master II

Eleni,

This approach won't work unfortunately. What you should keep in mind about set analysis is that it is calculated once per chart. For that reason, you can't use it to check which dimension you are currently in--only a conditional function like IF can do that. However, if I understand your question, this syntax should do the same thing more elegantly:

if(match(CostElement,'R1','R2','R3','R4'),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={"<=$(=Only(RM1_Period))"}>} RM1_Period_Value),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={$(=Only(RM1_Period))}>} RM1_Period_Value)

)

Regards,

Vlad

eleni_theodorid
Partner - Creator
Partner - Creator
Author

Dear Vlad,

First of all I want to thank you for your quick response. Your suggestion it would be the answer to my problem only if in the pivot table we get the correct number to the total (which is the most important for me).

Am I doing something wrong? Do you have to suggest anything else?

Thanks a lot,

Helen

vgutkovsky
Master II
Master II

I think the incorrect total is being caused by a problem with your data model--you have a synthetic key table in there. Try to get rid of that so your CostData and ProdKilos tables are linked to each other by 1 key only. 

eleni_theodorid
Partner - Creator
Partner - Creator
Author

Thank you Vlad,

I'm gonna try this and i will inform you.

Regards,

Helen

eleni_theodorid
Partner - Creator
Partner - Creator
Author

Dear Vlad,

although I have avoid the Synthetic Key and the Data Model is ok, the total of this expression in the pivot table display false results (the total in the Straight table is correct). Practicaly, it seems to do Expression Total instead of sum of Rows although in Total Mode the sum of Rows is marked.

Thanks,

Helen

vgutkovsky
Master II
Master II

Helen,

If you want sum of rows in a pivot table to match your current logic for the straight table, you would need to replace the expression with something like this:

sum(aggr(
if(match(CostElement,'R1','R2','R3','R4'),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={"<=$(=Only(RM1_Period))"}>} RM1_Period_Value),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={$(=Only(RM1_Period))}>} RM1_Period_Value)

)
,Desc))

But make sure this is actually what you want and it's not double-counting.

Regards,

Vlad

eleni_theodorid
Partner - Creator
Partner - Creator
Author

Vlad,

I want to thanks you one more time for your response.

I have already tried this but if you put it in the chart you will see that although the total are right, all the other values (cell values) are wrong.

Regards,

Helen

vgutkovsky
Master II
Master II

Helen,

I still think there's an underlying problem with the data model--the "BusinessUnit_Description" and "Desc" are not linked properly between themselves. You can try this expression, but the total is slightly different:

sum(aggr(
if(match(CostElement,'R1','R2','R3','R4'),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={"<=$(=Only(RM1_Period))"}>} RM1_Period_Value),

   sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={$(=Only(RM1_Period))}>} RM1_Period_Value)

)
,Desc,BusinessUnit_Description))

Alternatively, you can "trick" QlikView into showing you one expression for the total row and a different one for the cell rows:

if(Dimensionality()<>0,

sum(aggr(

if(match(CostElement,'R1','R2','R3','R4'),

    sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={"<=$(=Only(RM1_Period))"}>} RM1_Period_Value),

    sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={$(=Only(RM1_Period))}>} RM1_Period_Value)

)

,BusinessUnit_Description,Desc)),

sum(aggr(

if(match(CostElement,'R1','R2','R3','R4'),

    sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={"<=$(=Only(RM1_Period))"}>} RM1_Period_Value),

    sum({$<RM1_Year={$(=Only(RM1_Year))}, RM1_Period={$(=Only(RM1_Period))}>} RM1_Period_Value)

)

,Desc))

)

However, once you start writing expressions like this, the application quickly spins out of control. I suggest you start by investigating why showing BusinessUnit_Description and Desc in the same chart leads to different results that just using Desc as a dimension. My guess would be that it has something to do with the density of BusinessUnit_ID (that it's populated 72% of the time while CostElement is populated 67%).

Regards,

Vlad