topic Re: Cumulative Sum in load script using Peek? in App Development

Cumulative Sum in load script using Peek?

110 — Tue, 05 Nov 2024 11:05:54 GMT

So I wanted to roll up the entitlement consumption in the consumption monitor to see at what point in month we reached 50%/75% of our capcity.

I'm just tacking this on the end so as not to interfere with the usual QVD incremental load, however it seems to be duplicating rows and not summing as expected.

I first create a table that sums the CapacityUsed by Date & AppID , then use Peek.

[Cumulative]: NoConcatenate LOAD appId, MonthStart( Date(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ'))) AS CMLMonth, Date(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ')) AS DateOnly, // Extracts date portion from endTime Sum(capacityUsed) AS TotalCapacityUsed RESIDENT Entitlement_Consumption GROUP BY appId, Date(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ')), MonthStart( Date(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ'))); [MonthlyCumulative]: NoConcatenate LOAD appId,DateOnly as CMLDate, CMLMonth, // Get the month start for grouping by month If(CMLMonth = Peek('CMLMonth')AND appId = Peek('appId'), Peek('CumulativeCapacityUsed') + TotalCapacityUsed,TotalCapacityUsed) AS CumulativeCapacityUsed // Calculate cumulative sum RESIDENT [Cumulative] ORDER BY DateOnly,appId, CMLMonth; Drop Table Cumulative;

What I should eventually get is akin to the below

However it doesn't seem to be accumulating

Re: Cumulative Sum in load script using Peek?

Qrishna — Tue, 05 Nov 2024 11:08:32 GMT

Whats your Cumulative Calc expression?

Re: Cumulative Sum in load script using Peek?

Kushal_Chawda — Tue, 05 Nov 2024 11:14:03 GMT

@110 try to use rangesum()

If(CMLMonth = Peek('CMLMonth')AND appId = Peek('appId'), rangesum(Peek('CumulativeCapacityUsed'),TotalCapacityUsed),TotalCapacityUsed) AS CumulativeCapacityUsed

Re: Cumulative Sum in load script using Peek?

110 — Tue, 05 Nov 2024 11:47:07 GMT

@Qrishna - In the top chart it's using the built in accumulation modifier, but it's a bit of a struggle to unpick and repurpose into the load script.

Aggr(RangeSum(Above( (　sum(minutesUsed)　) , 0, RowNo())), ([$(=Replace(GetObjectField(1),']',']]'))], (Numeric, Ascending), (Text, Ascending)), ([$(=Replace(GetObjectField(0),']',']]'))], (Numeric, Ascending), (Text, Ascending)))

@Kushal_Chawda - I tried your code.

I'd have thought this was a simple thing - it's trivial to do in SQL.

Logic is- accumulate CapcityUsed by date, and start over if CML_Month =CML_Date,

The result should be a rolling sum that resets each month, per app - as you can see it's a bit wonky.

Here's the latest code - I was getting peculiar repeated dates so had to floor the date field.

[Cumulative]: NoConcatenate LOAD appId, Date(Floor(MonthStart(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ')))) AS CMLMonth, // Rounds to the start of the month as date Date(Floor(Timestamp#(endTime, 'YYYY-MM-DDThh:mm:ss.fffZ'))) AS DateOnly, // Rounds to the date only (no time) capacityUsed AS TotalCapacityUsed RESIDENT Entitlement_Consumption; [MonthlyCumulative]: NoConcatenate LOAD appId,DateOnly as CMLDate, CMLMonth, // Get the month start for grouping by month /*If(CMLMonth = Peek('CMLMonth')AND appId = Peek('appId'), Peek('CumulativeCapacityUsed') + TotalCapacityUsed,TotalCapacityUsed) AS CumulativeCapacityUsed // Calculate cumulative sum */ If(CMLMonth = Peek('CMLMonth')AND appId = Peek('appId'), rangesum(Peek('CumulativeCapacityUsed'),TotalCapacityUsed),TotalCapacityUsed) AS CumulativeCapacityUsed RESIDENT [Cumulative] ORDER BY DateOnly,appId, CMLMonth; Drop Table Cumulative;

Re: Cumulative Sum in load script using Peek?

Kushal_Chawda — Tue, 05 Nov 2024 11:53:45 GMT

@110 it seems issue with Order by. I think order by should be like below

ORDER BY appId,CMLMonth,DateOnly;

Re: Cumulative Sum in load script using Peek?

Qrishna — Tue, 05 Nov 2024 12:01:35 GMT

if possible share some sample data from table

Entitlement_Consumption

so i can recreate this scenario and look for any errors

Re: Cumulative Sum in load script using Peek?

110 — Tue, 05 Nov 2024 12:03:07 GMT

Thanks - I tried changing the ordering - maybe it's too complicated a thing to achieve in the load script?

Here in the chart, filtering on one month, final # should be 2,466

Trying to do the same ( but with pre-calculated cumulative sum ) - it goes a bit wonky

It'd be great if there's some form of simplified syntax for loads on the roadmap that'd facilitate this , akin to how Excel has conditional range sum or average, or even how SQL does it.

Re: Cumulative Sum in load script using Peek?

Kushal_Chawda — Tue, 05 Nov 2024 12:08:16 GMT

@110 You could do it on front end. If you could share some sample dummy data with expected output, we can suggest required expression and even script approach

Re: Cumulative Sum in load script using Peek?

110 — Thu, 07 Nov 2024 11:23:47 GMT

I've attached an example with some random data in the same format.

So column D would be the desired output, where it performs a running sum in ascending date order, over the 'MonthYear' field, so the sum value resets per month.

When charted , I'd expect to be able to produce the following visualization.

I can do this with the modifiers in Qlik, however I'd like to do it specifically in the load script as a flat table, so I can highlight thresholds ( in this case , the data being minutes consumed )

Hopefully that makes sense?

Re: Cumulative Sum in load script using Peek?

Kushal_Chawda — Thu, 07 Nov 2024 11:40:33 GMT

@110 try below expression. Assuming your Date & MonthYear are formatted as Numeric.

Replace sum(CountDaily) with your actual count expression

=sum(aggr(rangesum(above(sum(CountDaily),0,RowNo())),(MonthYear,(NUMERIC)),(Date,(NUMERIC))))

Re: Cumulative Sum in load script using Peek?

110 — Tue, 26 Nov 2024 11:20:02 GMT

Thanks @Kushal_Chawda - that worked - I can now highlight where the running sum intersects the threshold. Great stuff, and thanks again for your help!

As below- conditional colouring where intersections hit:-