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: 
Anonymous
Not applicable

Set Analysis with If Statements

Hello Qlikkers I am struggling with this situation and I was hoping if you can help me out.

I have a pivot table that I want to pull those records with below conditions

1) if the sales by Region >= 1 mil or if Q_sold >=50K

2) if the Rate for max month is >=1% at merchandise level

here is the hierarchy of data:

Region, Country, Sale_Month, State, Merchandise

condition 1) is to be fullfilled at Region level and 2) at Merchandise level

I have it somewhat working by having 5 dim:-

dim1)

calc. dimension as-----

If((aggr(sum(Sales)>=1000000,Region)or(aggr(sum(Q_Sold)>=50000,Region)

)),aggr( if(sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_per)/sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_Total)

>=0.01,Region), Region))

dim2)  Country

dim3) Sale_Month

dim4) State

dim5) Merchandise

and as expression I have:

if(sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_per)/sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_Total)

>=0.01,sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_per)/sum({$<Sale_Month={'$(vMaxMonth)'}>}Sales_Total))


all is well but the problem is this pivot table only works whne I select values in my Sale_Month List Box. if I clear all, the table is blank.

how to fix this.

1 Reply
felipedl
Partner - Specialist III
Partner - Specialist III

Hi John,

Seems to me that your vMaxMonth variable is only being evaluated when you select a value on the Sale_Month field.

Can you share a screenshot or the app to see what's going on?


If you have named the expression, remove the name and hover the mouse over the expression to check what's the outcome of your expression.