Set Analysis with If Statements

Anonymous — Fri, 14 Sep 2018 21:02:22 GMT

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.

Re: Set Analysis with If Statements

felipedl — Mon, 17 Sep 2018 11:58:17 GMT

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.