Display cumulative values

[Zeta]

Dear Community,

I'm stuck on a problem I can't seem to solve.

Here is a table (see attachment).

I want to create a line chart. The dimension is the contract end date. Multiple contracts may have the same contract end date.

The measure is the total number of cars still under contract on the given date.

The desired result is in Column D. The explanation of the result is in Column E.

How should the measure be formulated to produce the desired result?

Thank you very much for your help!

Best regards

[alexquimu · Accepted solution]

Hey @Zeta,

For a front-end solution, I recommend using RangeSum combined with Aggr. The Aggr function is necessary to ensure the measure remains robust even when custom sorting is applied to the table.

Sum(Aggr(RangeSum(Below(Sum( [Total Cars]), 0, NoOfRows())), (Contract, (NUMERIC, ASCENDING))))

You can also use the Contract End Date in the Aggr function if you don't have a Contract ID or simply prefer not to create that field

Sum(Aggr(RangeSum(Below(Sum( [Total Cars]), 0, NoOfRows())), ([Contract end date], (NUMERIC, ASCENDING))))

Script solution: compute that column in the edit script section with RangeSum + peek() functions. 

Temp_Table:
LOAD
    Contract,
    "Contract end date",
    "Total Cars"
FROM [lib://Qlik_Data/Qlik.xlsx]
(ooxml, embedded labels, table is Tabelle1);

Final_Table:
NoConcatenate
LOAD
    Contract,
    "Contract end date",
    "Total Cars",
    RangeSum("Total Cars", Peek("Cumulative values")) as "Cumulative values"
RESIDENT Temp_Table
ORDER BY "Contract end date" DESC;

DROP TABLE Temp_Table;

You get the same results for every approach:

Find attached the .qvf

Regards,

[NitinK7]

Hi Zeta,

This is very interesting Scenario. I tried to achieve your required output. please see below, May be this will help to you.

Script:

To load Raw data calculate Cumulative sum

CAR:
Load
	Contract,
    Date(Date#("Contract end date",'DD-MM-YY'),'DD/MM/YYYY')  As "Contract end date",
    Ceil(Month( Date(Date#("Contract end date",'DD-MM-YY'),'DD/MM/YYYY')) /4) As Month,
    "Total Cars";

Load * Inline [
Contract,	"Contract end date",	"Total Cars"
1,	30-04-26,	100
2,	31-07-26,	100
3,	30-11-26,	100
4,	30-06-27,	100
5,	31-12-32,	100
];

Temp_MaxDays:
LOAD
     max(Contract) as MaxContract
RESIDENT CAR;

let vMaxContract = peek('MaxContract', 0, 'Temp_MaxDays'); Drop Table Temp_MaxDays;
for vOuterLoop = 0 to vMaxContract
     for vInnerLoop = vOuterLoop to vMaxContract
          AccumulatedDays:
          LOAD
              peek("Contract end date", $(vOuterLoop), 'CAR') 	As date,
              peek("Total Cars", $(vInnerLoop), 'CAR') 			As CARS
          AUTOGENERATE(1);
     next
next

Left Join(CAR)
Load
	date As "Contract end date",
    Sum(CARS) As Output_Car
    Resident AccumulatedDays where Len(date)>0 
    Group by date ; Drop Table AccumulatedDays;

Create Master Calendar (For 4 month of span for Chart Dimention ) 

min_max:
Load
	Min("Contract end date") as minDate,
	Max("Contract end date") as maxDate
Resident CAR;

Let vMinDate = Peek('minDate',0,'min_max');
Let vMaxDate = Peek('maxDate',0,'min_max');

drop table min_max;

Master_Calendar:
Load
	Delivery_Date, 
    AddMonths(Date(MonthStart(Delivery_Date, -Mod(Month(Delivery_Date)-1, 4)), 'DD/MM/YYYY'),3) as FourthMonth,
    Ceil(Month(Delivery_Date) / 4) As MonthNumber;
Load
	Date($(vMinDate) + IterNo() - 1) as Delivery_Date
	AutoGenerate 1 While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);



Temp:
Load
	Year(FourthMonth) &'|'& MonthNumber 			 As Key,
    FourthMonth
    Resident Master_Calendar; Drop Table Master_Calendar;

Left Join (Temp)

Load	
	Year("Contract end date") &'|'& Month			 As Key,
	Contract,
    "Total Cars",
    Output_Car
    Resident CAR; Drop Table CAR;

Final Table ( reflate previous value when Total Cars are 0 or null)

Final:
Load
	FourthMonth,
    Contract,
    "Total Cars",
    If(Output_Car=0 or IsNull(Output_Car),Peek(Output_Car),Output_Car) As Output_Car
    Resident Temp; Drop Table Temp;

[Zeta]

Thank you Alex,

Sum(Aggr(RangeSum(Below(Sum( [Total Cars]), 0, NoOfRows())), ([Contract end date], (NUMERIC, ASCENDING))))

is the solution I need.

Best regards

[Zeta]

Thank you NitinK7.

Alex's solution is sufficient.

Best regards