How to calculate cumulative value in Qlik Sense load script?

[MayaKe]

I am trying to calculate the cumulative inventory value of IMPQTY1 for every given product (stored in the same warehouse and the same company) by adding up the closing inventory for every month of every year .  I tried using the following load script and the result I get is totally messed up:

FD.INVENTORY.TMP1:

LOAD

    COMPANY,

    YEARMONTH,

    FISCPRD,

    PERIOD,

    MTRL,

    WHOUSE,

    IMPQTY1

Resident FD.INVENTORY.TMP
order by COMPANY,MTRL, WHOUSE, YEARMONTH desc

NoConcatenate
FD.INVENTORY:
LOAD
COMPANY,
YEARMONTH,
FISCPRD,
PERIOD,
MTRL,
WHOUSE,
IMPQTY1,
if(peek('COMPANY')=COMPANY AND peek('MTRL')=MTRL AND peek('WHOUSE')=WHOUSE, Rangesum(Peek('Rolling.IMPQTY1')+IMPQTY1), IMPQTY1) as Rolling.IMPQTY1
Resident FD.INVENTORY.TMP1
;

[Gabbar]

You used the correct way but didnt fix the order of things,
Use order by on all the dimension in if statement as required(check their order while writing them in order by statement) and also use order for your date/month(last, after all other fields) field to sort it in ascending order.

[MayaKe]

Thank you for your comment but I am not quite sure if I got this right.

I am do order by on all the dimensions that are also in my if statement -> order by COMPANY,MTRL, WHOUSE, YEARMONTH desc

if(peek('COMPANY')=COMPANY AND peek('MTRL')=MTRL AND peek('WHOUSE')=WHOUSE, Rangesum(Peek('Rolling.IMPQTY1')+IMPQTY1), IMPQTY1) as Rolling.IMPQTY1

Also, I do YEARMONTH last and I tried asc instead of descending but the result was still wrong.

[Gabbar]

Can you please check what if the format of your yearmonth field, the reason could be that it could be formatted like 
'MMM YYYY' as Jan 2023 in which it can be order perfectlt, try changin it to
YYYYMM as in 202301 in that way it will order perfectly.
Also please share a Sample shot of input and output data if this doesnt solve it.

[MeehyeOh]

Hi @MayaKe 

I think you should use 'sortorder'.

This is my script that refered 'RollingInv.png' image you attached.

-Script

TMP:

LOAD

    COMPANY,

    YEARMONTH,

    FISCPRD,

    PERIOD,

    MTRL,

    WHOUSE,

    IMPQTY1

FROM [lib://~~~/Data/test/누적.xlsx] (ooxml, embedded labels, table is Sheet1);

CUMULATICE:

NoConcatenate

Load

RecNo() As No,

    COMPANY,

    WHOUSE,

    MTRL,

    FISCPRD,

    PERIOD,

    YEARMONTH,

    IMPQTY1,

    If(COMPANY = Peek(COMPANY) And WHOUSE = Peek(WHOUSE) And MTRL = Peek(MTRL), IMPQTY1+Peek(ROLLING_IMPQTY1),IMPQTY1) As ROLLING_IMPQTY1

Resident TMP

Where COMPANY <> Null()

Order By COMPANY DESC,WHOUSE DESC, MTRL DESC, FISCPRD DESC, PERIOD DESC ;

Drop Table TMP;

-Result

Reply, thanks!