Roll Up Totals in Chart Based on Multiple Selected Values

Kodafixed — Wed, 04 May 2022 15:49:39 GMT

I have a BI dashboard that needs to show the Top 10 performing customers based on the viewer's selection of date(s) and area(s). The underlying table data stores the Sales Period (YYYY-MM), Sales Area, Customer Name, and the total invoiced revenue from them for that period.

In my straight table chart I have a calculated field to get the top-10 customers by sales and it seems to work if the user selects at least 1 and only 1 of either|both Period & Area, but if they select multiples of either or select none of either it breaks.

= MONEY( AGGR( IF( RANK(TOTAL SUM({<[Customer Name]>} [Customer Sales])) <= 10, [Customer Sales]), [Customer Sales]) , '$ #,##0' )

What I am needing the chart to do is, if the user selects multiple Periods &/or Areas, or none of either, that the top-10 returned is the "aggregate" sum of all Periods/Dates in the current selection as a single entry.

I've been googling and reading solutions for the last day and none of what I'm finding addresses this issue. Thanks in advance!

Re: Roll Up Totals in Chart Based on Multiple Selected Values

Kodafixed — Fri, 06 May 2022 13:52:05 GMT

Fixed it!

= IF( AGGR(RANK(SUM(Sales)), Customer) <= 10 , AGGR(SUM(Sales), Customer) , Null() )

topic Re: Roll Up Totals in Chart Based on Multiple Selected Values in QlikView

Roll Up Totals in Chart Based on Multiple Selected Values

Re: Roll Up Totals in Chart Based on Multiple Selected Values