topic Set Analysis with If() in QlikView

Set Analysis with If()

eleni_theodorid — Tue, 04 Oct 2011 15:04:34 GMT

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

Set Analysis with If()

vgutkovsky — Tue, 04 Oct 2011 15:40:38 GMT

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

Set Analysis with If()

eleni_theodorid — Tue, 04 Oct 2011 16:11:33 GMT

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

Set Analysis with If()

vgutkovsky — Tue, 04 Oct 2011 16:36:55 GMT

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.

Set Analysis with If()

eleni_theodorid — Wed, 05 Oct 2011 07:10:27 GMT

Thank you Vlad,

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

Regards,

Helen

Re: Set Analysis with If()

eleni_theodorid — Wed, 05 Oct 2011 11:16:07 GMT

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

Set Analysis with If()

vgutkovsky — Wed, 05 Oct 2011 18:08:48 GMT

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

Set Analysis with If()

eleni_theodorid — Thu, 06 Oct 2011 06:53:02 GMT

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

Set Analysis with If()

vgutkovsky — Thu, 06 Oct 2011 14:21:09 GMT

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