topic If Statement and Set Analysis in QlikView

If Statement and Set Analysis

Thu, 28 Apr 2011 21:32:00 GMT

Hi I have two problems but probably only need one of them solving.

I am trying to add sets of costs based on a product group, 2 product groups fall into the same category and all the rest in the second category.

Previously I created expressions for all the individual costs and then used an IF statement to add them together such as

=IF([ProductGroup] ='023' or [ProductGroup]='025', [COST1]+[COST2], [COST3]+[COST4])

where [COST1] , [COST2] etc are expressions within the chart.

This works fine for individual lines but for the partial sum I get the sum of the else clause ie just [COST3]+[COST4]

If I use set analysis and try enter [COST1] , [COST2] etc I get Bad Field name so assume I can't use an expression within the set analysis?

As I say line by line this works with the if Statement but not for the total. Any ideas much appreciated.

Thanks

Matt

If Statement and Set Analysis

erichshiino — Thu, 28 Apr 2011 21:40:43 GMT

You can not use chart expressions inside set analysis.

If you are using straight table you can set the expression to be sum of lines intead of expression total (check on the right side of the tab 'Expressions' when you select each expression.

Maybe you could rewrite the expressions to have a total that makes sense.

if COST1= sum(COST1), your set analysis would be sum( {<[ProductGroup]={'023','025'}>} COST1) and so on...

If Statement and Set Analysis

Thu, 28 Apr 2011 21:57:21 GMT

Many thanks for your quick reply. Unfortunately COST1 isn't quite that straightforward it looks more like the below so with several to add in it's going to get messy in the Set Analysis.

(Measured='Measured' and (only(left([AccountNo],4))='A900' or only(left([AccountNo],4))='S001'),sum(QTY * NotionalCostMeasured * LINK_TO_STOCK), if(StockCode='1307',(sum(CCost)/LINK_TO_STOCK),sum(CCost))) + sum(QTY * [NAVNotionalCost]

)

Matt

If Statement and Set Analysis

erichshiino — Thu, 28 Apr 2011 22:04:48 GMT

What about the other option? Is it a straight table where you can set the total mode for expressions?

another option would be to use aggr() like this:

=sum( aggr( your_original_expression_here), dimension_on_chart1,dimension_on_chart2, ...)

If Statement and Set Analysis

Thu, 28 Apr 2011 22:28:03 GMT

Thanks Erich, it was a Pivot table but not for any real reason, set to Straight table and it works.

Will try the aggr() too

Many thanks

Matt