Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
bugsysiegals
New Contributor

How-To: % of Sales with One Dimension/Measure and Zero-to-Many Applied Filters??

I need to calculate the cumulative % of sales by customer to identify which customers make up our top X% of sales.  I also need to do the same for products but would create a separate expression for that dimension.  That said, users want to be able to apply various filters related to customers or products and have the % of sales re-calculated in real-time.

Example granularity of Sales data below ...

DateCustomerRegionTerritoryStateProductProduct LineProduct TypeSales Revenue
201801A1AA1231A$100
201801A1AA2342B$200
201801B2AB1231A$200
201801B2AB1231A$300
201801C2BC3452C$700

 

The expression must calculate aggregate sales for the dimension, ideally sort the dimension in descending order by sum of sales in case the user has the table sorted by something else, and show the cumulative sales and cumulative % of sales based on the currently selected data (zero-to-many) filters applied.  Please see below for additional clarity ... is this possible?

 

Ex. Sales by Customer - No Filters

CustomerSalesCumulative SalesCumulative % of Sales
C$700$70047%
B$500$1,20080%
A$300$1,500100%

 

Ex. Sales by Customer - Filtered to Region 2

CustomerSalesCumulative SalesCumulative % of Sales
C$700$70058%
B$500$1,200100%

 

Ex. Sales by Product - No Filters

ProductSalesCumulative SalesCumulative % of Sales
345$700$70047%
123$600$1,30087%
234$200$1,500100%

 

Ex. Sales by Product - Filtered to Region 2

ProductSalesCumulative SalesCumulative % of Sales
345$700$70054%
123$600$1,300100%

 

Please help ... I cannot figure this out.

Labels (2)
1 Reply

Re: How-To: % of Sales with One Dimension/Measure and Zero-to-Many Applied Filters??

Try this

Aggr(RangeSum(Above(Sum([Sales Revenue]), 0, RowNo())), ($(='[' & GetObjectField(0) & ']'), (=Sum([Sales Revenue]), desc)))