QlikSense Saas - Cumulative YTD without using RangeSum & Above

526 Views Asked by At

Trying to create the Measure below Cumulative that is basically a cumulative YTD calculation applied to the Amount column (Amount = Count(ORDER_ID)).

That print screen was taken from the desktop app, that natively integrates a cumulative option within the graph components. That's not an option in my context where I need to use the Saas version.

Qlik Desktop - Cumulative YTD

1

I've seen many articles in the community that suggest to use a combination of rangesum and above but I'm trying to avoid that.

Coming from the Power BI world, I was used to calculate it with a Calculate(Count(ORDER_ID), ALL(Date), DatesYTD(Date), Date<= max(Date)). The All() removes all context filtering to the measure, and we then filter according to what we need, applying a set varibale max(Date), to ensure a cumulative calculation of the amount month to month.

I haven't found yet a good equivalent of the All() Power BI function that actually removes the context filtering in a pivot table/graph (here the breakdown by the month dimension), to allow us a to then apply a different filtering for instance in our case on a cumulative YTD basis.

I've tried something with total() in my set analysis, see below, but it's not working:

Count ( Distinct total< [Date.autoCalendar.Year], Date> ${<RECORD_TYPE = {'Declared Transactions'}, Date >=${"=YearStart(MAX(Date))"}, Date<=${"=MAX(Date)"} >} ORDER_ID )

which gives that result:

Qlik Saas - Wrong cumulative YTD measure attempt

Basically that's my problem: Qliksense documentation

0

There are 0 best solutions below