QlikSense Saas - Cumulative YTD without using RangeSum & Above

MikaelFriederich — Thu, 18 Aug 2022 17:46:34 GMT

Hi,

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.

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:

Any hints here?

Re: QlikSense Saas - Cumulative YTD without using RangeSum & Above

SunilChauhan — Fri, 19 Aug 2022 05:31:02 GMT

try this in cumulative column

RangeSum(Above(sum(Amount),0,RowNo()))

Re: QlikSense Saas - Cumulative YTD without using RangeSum & Above

MikaelFriederich — Fri, 19 Aug 2022 14:14:29 GMT

@zlam , that works but I'm trying to avoid that method that is dependent on how you sort your table.

I'd rather remove the Month dimension filtering, and recalculate my cumulative YTD value month to month.

topic Re: QlikSense Saas - Cumulative YTD without using RangeSum & Above in App Development

QlikSense Saas - Cumulative YTD without using RangeSum & Above

Re: QlikSense Saas - Cumulative YTD without using RangeSum & Above

Re: QlikSense Saas - Cumulative YTD without using RangeSum & Above