Cumulation in Load Script => fill missing month postings

[ChrisGer]

Hi Community,

I need your help on a load script. 

I created this Qlik Script:

Table_CUM:

Load

Year,

Month,

YearMonth,

Order,

Orderpos,

Account,

Value,

if((Previous(Orderpos)=Orderpos and Previous(Account)=Account,rangesum(peek('Value_cum'),Value),Value) as Value_cum

Resident Table

Order by Orderpos ASC, Account ASC, YearMonth ASC;

This Script cumulates all the values; but I want all the Months being filled with cumulated results in Column Value_cum (see Result vs. Desired result). How could I achieve this? 

Kind regards

Christian

[Firefly_cam]

Hey Christian,
I would suggest to use best practices of building data models, please check this out The As-Of Table .
But if you really insist on your variation:

InitialTable:
Load * inline [
Year, Month, YearMonth, Order, Orderpos, Account, Value
2023, 07, 202307, 1002,	1002-700, Revenue, 384400
2023, 12, 202312, 1002, 1002-1000, Revenue, 1150000
];

Join(InitialTable)
Load
  Year(Date)*100+Month(Date) as YearMonth;
Load 
  Monthstart(DateMin,IterNo()-1) as Date
While Monthstart(DateMin,IterNo()-1)<=DateMax;
LOAD
  min(datefield) as DateMin,
  MonthStart(Today()) as DateMax
;
LOAD
  MakeDate(left(FieldValue('YearMonth', RecNo()),4),Right(FieldValue('YearMonth', RecNo()),2)) as datefield
AutoGenerate FieldValueCount('YearMonth');

ResultTable:
Load *,
if(Previous(Orderpos)=Orderpos and Previous(Account)= Account,rangesum(peek('Value_cum'),Value), Value) as Value_cum;
Load
  num(left(YearMonth,4)) as Year,
  right(YearMonth,2) as Month,
  YearMonth,
  alt(Order,PEEK(Order)) as Order,
  Coalesce(Orderpos,PEEK(Orderpos)) as Orderpos,
  Coalesce(Account,PEEK(Account)) as Account,
  alt(Value,0) as Value;
Load *
Resident InitialTable
Order By YearMonth asc;
Drop table InitialTable;

[ali_hijazi]

you create a calendar from your minimum available date till today for example from the beginning of a desired year:

let StartDate = num(DayStart(YearStart(MakeDate(2000))));

let EndDate = num(daystart(monthend(Today())));

//Create a temporary calendar

      TempCalendar:

      load recno() as Date_Key,

      '$(StartDate)'+recno()-1 as PeriodDate

      autogenerate(EndDate-StartDate+1);

       MasterCalendar:

load 

Year(PeriodDate) as YEAR,

Month(PeriodDate) as MONTH,

Num(Month(PeriodDate)) as MONTH_NUM,

resident TempCalendar  order by PeriodDate Asc;

Drop table TempCalendar;

then you outer join your fact table with this calendar

now you sort your fact table by account and date

if previous(account) <> account, amount, peek(cum_amount) + amount) as cum_amount

...