Cumulative Sum in load script using Peek?

[110]

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 

[Qrishna]

Whats your Cumulative Calc expression?

[Kushal_Chawda]

@110  try to use rangesum() 

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

[110]

@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;

[Kushal_Chawda]

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

ORDER BY appId,CMLMonth,DateOnly;

[Qrishna]

if possible share some sample data from table

Entitlement_Consumption

so i can recreate this scenario and look for any errors

[110]

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. 

[Kushal_Chawda]

@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

[110]

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? 

[Kushal_Chawda · Accepted solution]

@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))))