Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.