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

TOTAL/ALL issue to modify AGGR as the set analysis for AGGR does not work as expected

Dear all,

I encountered an issue applying the set analysis to an AGGR to diregards current selections.

Here's a simplied expression as an example:

Fractile(AGGR(SUM({1<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), DEALER_ID), 0.1)

The scenario is that some other KPIs show the MTD value when the certain month is selected while YTD 90th percentile is expected in this expression.

The problem is that  the calculation result of the expression still changes when the RETAIL_MONTH selected value changes even though the fixed range has been provided in above expression for retail month as described above..

According to some data verification, it seems the DEALER_ID in above expression is still controled by the selection criteria. E.g. if I multi-select 201801, 201802, 201803.... 201809, I would get the expected result for this expression. If I only select 201809 as RETAIL_MONTH, the dealers which do not TOTAL_REVENUE in 201809 would not be included in the Fractile expression.

I also tried the following expressions:

Fractile(TOTAL AGGR(SUM({1<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), DEALER_ID), 0.1)

Fractile(TOTAL<RETAIL_MONTH> AGGR(SUM({1<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), DEALER_ID), 0.1)

None working as expected.

The only thing working fine is

Fractile(ALL AGGR(SUM({1<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), DEALER_ID), 0.1)

But another problem occurs as if there are 2 or more structure paramaters in AGGR expression and we need all dealers under selected BRNAD included into the percentile calculation regardless of the other selections.

Fractile(ALL AGGR(SUM({<RETAIL_MONTH)={"<=201809 >=201801", BRAND={"$(V_SELECTED_BRAND)"}}>}TOTAL_REVENUE), BRAND, DEALER_ID), 0.1)

This seems disregard the brand selection as well which is not expected

Anyone has any idea how to disregards the selection criterias on the dimension DEALER_ID within the AGGR?

Thanks & Best regards,

Jason

消息编辑者为:Jason Wei

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Have to use the same set expression within the Fractile again to disregards the selection or regards to some selections, e.g.

Fractile(<RETAIL_MONTH)={"<=201809 >=201801"}> AGGR(SUM({<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), BRNAD, DEALER_ID), 0.1).


In this way, it is more flexible whether disregards some selections or dimentions instead of using ALL modifier.

Only use set expression in the AGGR does not work as expected.

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Resolved.

vishus913
Partner - Creator
Partner - Creator

Can u please tell how it was resolved?

Anonymous
Not applicable
Author

Have to use the same set expression within the Fractile again to disregards the selection or regards to some selections, e.g.

Fractile(<RETAIL_MONTH)={"<=201809 >=201801"}> AGGR(SUM({<RETAIL_MONTH)={"<=201809 >=201801"}>}TOTAL_REVENUE), BRNAD, DEALER_ID), 0.1).


In this way, it is more flexible whether disregards some selections or dimentions instead of using ALL modifier.

Only use set expression in the AGGR does not work as expected.