Clarification Needed on Qlik Sense Expression for Dynamic Dimension in Bar Chart

Fq95 — Fri, 05 Jan 2024 12:07:52 GMT

I am currently facing an issue with a Qlik Sense expression used in a bar chart to display the top 15 products with the highest revenue growth. The expression is as follows:

Aggr(
    if(
        rank(
            SUM({<Date = {"$(='>=' & Date($(vDateMaxLimit), 'YYYY-MM-DD')& '<=' & Date(Max(Date), 'YYYY-MM-DD')"} ,MonthNum=,Year=,Quarter=>} Revenue)
            -
            SUM({<Date = {"$(='>=' & Date($(vDateMinLimit), 'YYYY-MM-DD') & '<=' & Date($(vDateMiddleLimit), 'YYYY-MM-DD')"} ,MonthNum=,Year=,Quarter=>} Revenue)
        ) <= 15, 
        [Product Marketing Name]&[Product]&[Product Group]&[Product Series]&[Product Description]
    ), 
    [Product Marketing Name]
)

The expression, used as a dynamic dimension, aims to showcase the top 15 products with the highest revenue growth. However, it seems to aggregate the revenue for each [Product Marketing Name] value, resulting in unintended totalizations for different combinations.

For example, considering the data below:

Product Marketing Name	Product	Product Group	Product Series	Product Description	Revenue Growth
Saphire b67 5G 128GO	SMARTPHONE	MOBILE	A50 SERIES	HHP MOBILE,SM-A546B/DS,BLACK,EUB	€ 1,132,110
Saphire b67 5G 128GO	SMARTPHONE	MOBILE	A50 SERIES	HHP MOBILE,SM-A546B/DS,BLACK,EEB	€ 89,676
Saphire b67 5G 128GO	SMARTPHONE	MOBILE	A50 SERIES	HHP MOBILE,SM-A546B/DS,WHITE,EUB	€ 4,862

In the bar chart, it currently shows the total revenue growth for the Product Marketing Name "Saphire b67 5G 128GO" as € 1,226,648, combining different combinations for the other dimensions. However, I would like to keep each combination separated in the bar chart, even if a Product Marketing Name appears more than once.

I would appreciate your expertise in helping me understand and rectify this issue.

Thank you in advance for your time and assistance.

Best regards,

Re: Clarification Needed on Qlik Sense Expression for Dynamic Dimension in Bar Chart

vinieme12 — Mon, 08 Jan 2024 03:13:52 GMT

if you want to separate by more dimensions then just add/remove them the aggregation scope

=Aggr( if( rank( SUM({<Date = {"$(='>=' & Date($(vDateMaxLimit), 'YYYY-MM-DD')& '<=' & Date(Max(Date), 'YYYY-MM-DD')"} ,MonthNum=,Year=,Quarter=>} Revenue) - SUM({<Date = {"$(='>=' & Date($(vDateMinLimit), 'YYYY-MM-DD') & '<=' & Date($(vDateMiddleLimit), 'YYYY-MM-DD')"} ,MonthNum=,Year=,Quarter=>} Revenue) ) <= 15, [Product Marketing Name]&[Product]&[Product Group]&[Product Series]&[Product Description] )

, [Product Marketing Name]

,[Product]

,[Product Group]

,[Product Series]

,[Product Description]

)

topic Clarification Needed on Qlik Sense Expression for Dynamic Dimension in Bar Chart in App Development

Clarification Needed on Qlik Sense Expression for Dynamic Dimension in Bar Chart

Re: Clarification Needed on Qlik Sense Expression for Dynamic Dimension in Bar Chart