Qlik Sense - rangesum not working when filters applied

Pete673 — Fri, 02 Apr 2021 07:53:54 GMT

Hi Guys

3 weeks into Qlik Sense and loving it!

I have an expression:

If([Heading-MAVehSales01] = 'Net Vehicle Sales', RangeSum(Above($(vMTDCurActMoney),0,4)),99999)

vMTDCurActMoney is a variable as below:

Sum({<MACat01 = {'Money'},Stream = {'Actual'},[TYPE_KEY] = {'CURRENT'}>} Amount)

On the sheet I have 4 Filters, YEAR, MONTH, Dealership, Dept.

The above formula works fine without filters and returns the correct result but with filters I just get zero??

Background here is that I am trying to write a Profit and Loss Sheet where subtotalling is a bit tricky. I have tried other suggestions for this but they all seem to have their limitations; TransposedPivotTable would take too long to write and you appear to be unable to format columns, PL Smart pivot, whilst useful will not cater for the column count I have in mind. Pivot tables themselves are too slow with 7 tiers of data (which is what I need to report. So I am back to either Table or Pivot table with subtotalling within a single tier. This is faster and will only require 3 tiers.
The issue in essence is that a dimensions "Gross Sales", "Discounts" and "overallowances" associate with GL codes and the "Net Vehicle Sales" is the sum of these.

So in short I need help on:

1. Making the rangesum expression work with filters

2. Any other inspiration to alternatively present a 7 tier PT so that it's quicker.

I attach my file

topic Qlik Sense - rangesum not working when filters applied in App Development

Qlik Sense - rangesum not working when filters applied