Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
manoj23_86
New Contributor II

Ignore selections on certain fields when a calculated measure is selected

In my Qliksense report I have a calculated measure like below (simplified it)

=if(aggr(sum(QTY), ITEM,FCST_WEEK)>var_Limit,'YES')

Basically if the user selects 'YES' for this expression, report selects the fields for which the aggregated QTY at ITEM and FCST_WEEK level is greater than the value in the variable.

What I want is, when the 'YES' is selected for the above expression value, report needs to select just ITEM and ignore FCST_WEEK i.e. User just wants to filter only the ITEMs that are selected from the expression above. By default it selects both ITEM and FCST_WEEK.

I hope the question is clear.

So is there way to clear the selection on FCST_WEEK and keep only the ITEMs selected?

Thanks,

Manoj

4 Replies
rachel_delany
Contributor

Re: Ignore selections on certain fields when a calculated measure is selected

Would something similar to the below work for what you are trying to do?

=if(aggr(sum(QTY), ITEM,FCST_WEEK)>var_Limit,aggr(sum(QTY), ITEM),aggr(sum(QTY), ITEM,FCST_WEEK))


E.g. the IF will check if the item has met the condition and then calculate accordingly.

manoj23_86
New Contributor II

Re: Ignore selections on certain fields when a calculated measure is selected

Thanks. Unfortunately this won't work. The second aggr by ITEM calculated for the subset of ITEMs from the first. Hence it still selects those FCST_WEEK.

rachel_delany
Contributor

Re: Ignore selections on certain fields when a calculated measure is selected

Why does FCST_WEEK need to included in the calculation if you don't want a subset based on it?

Are you able to share the file?

manoj23_86
New Contributor II

Re: Ignore selections on certain fields when a calculated measure is selected

The actual expression is a MAPE calculation between a baseline quantity and forecast. The MAPE is calculated at ITEM and FCST_WEEK level.

The user wants to select all ITEMs for which the MAPE is more than var_Limit. So if I don't include FCST_WEEK in my aggr. then the MAPE calculation will go wrong.

For the file, let me create a mock up report with dummy data and share it here.