Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Resolved.
Can u please tell how it was resolved?
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.