topic Re: Using Set Analysis with If Condition based on Dimensional Value in QlikView

Using Set Analysis with If Condition based on Dimensional Value

marc_wintjen — Wed, 22 Feb 2012 23:14:16 GMT

Hi, I'm trying to build an Expression against a 2 dimensional Chart (Year and Category) and I want to use set analysis when the Category = "Budget vs Actual" but a normal expression like "=Sum([Gross Sales])"when the Category is anything else. The Categories are "Budget, Actual, Variance" and when I try to calculate the Set Analysis of "

sum({<Category = {'Budget'} >} [Total Discount]) / sum({<Category = {'Budget'} >} Gross Sales])

Using Set Analysis with If Condition based on Dimensional Value

CELAMBARASAN — Thu, 23 Feb 2012 04:33:46 GMT

Hi,

Try with this expression

=if(Category='Budget vs Actual',Sum([Gross Sales]), sum( [Total Discount]) / sum(TOTAL <Year,Category> [Gross Sales]))

Hope it helps

Celambarasan

Re: Using Set Analysis with If Condition based on Dimensional Value

lironbaram — Thu, 23 Feb 2012 04:47:42 GMT

hi check the attach file

the discount test expression

you cant use set analysis because it only selects a category as catgeory is in your dimension it cant work

the trick is to use total in your expresssion

Re: Using Set Analysis with If Condition based on Dimensional Value

marc_wintjen — Thu, 23 Feb 2012 15:18:03 GMT

That worked perfectly - Thanks! I didn't realize you can do an if statement inside of an aggregation!