Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

bigd_bloomberg
New Contributor

Using Set Analysis with If Condition based on Dimensional Value

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])
Tags (2)
1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

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

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

3 Replies

Using Set Analysis with If Condition based on Dimensional Value

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

lironbaram
Honored Contributor II

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

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

bigd_bloomberg
New Contributor

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

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

Community Browser