Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
StacyCui
Creator
Creator

Aggr() function with aggregation dimension

Hi, We have a request.  We have some products . For the red one, we need aggregate together as a new dimension. For the new dimension, the calculate logic is also different with other dimensions. 

StacyCui_0-1675416215283.png

here comes my expression:

Dimension:

=if(WildMatch(DE,'FA DE Enabled' ,'FA DE Design' ,'FA DE' ,'FA DE Edge&AI' ,'FA DE Design Service Enable' ,'FA Digital Solution')>0 ,'FA Digital Solution' ,DE)

Measure:

if(WildMatch(DE,'FA DE Enabled' ,'FA DE Design' ,'FA DE' ,'FA DE Edge&AI' ,'FA DE Design Service Enable' ,'FA Digital Solution')=0
,sum(aggr(Sum({<[FiscalYear]={$(v_CurrentYear)}>}[OR Value RMB]),DE)),
Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Enabled"}>}FA_DE_Enable_OR)
+Sum( {<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Design"}>}[OR Value RMB])
+Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Design Service Enable"}>}FA_DE_DesignServiceEnable_OR)
+Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Edge&AI"}>}[OR Value RMB]))

I found it changes after I select one category. Is it related to aggr() ? 

StacyCui_1-1675416499365.png

StacyCui_2-1675416569435.png

If I delete aggr() function in the measures expression as follow. For some category, It could not be aggregated. How to solve it? Thanks so much.

if(WildMatch(DE,'FA DE Enabled' ,'FA DE Design' ,'FA DE' ,'FA DE Edge&AI' ,'FA DE Design Service Enable' ,'FA Digital Solution')=0
,Sum({<[FiscalYear]={$(v_CurrentYear)}>}[OR Value RMB]),
Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Enabled"}>}FA_DE_Enable_OR)

+Sum( {<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Design"}>}[OR Value RMB])
+Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Design Service Enable"}>}FA_DE_DesignServiceEnable_OR)
+Sum({<[FiscalYear]={$(v_CurrentYear)},DE={"FA DE Edge&AI"}>}[OR Value RMB]))

StacyCui_0-1675417036812.png

Labels (3)
1 Reply
Mark_Little
Luminary
Luminary

HI 

I would look at starting to address this in the Script. Make you new grouping as a new Dimension, then you can look at making the set analysis easier in the front end with an if based around the new dimension