Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikers,
i have a problem simple to understand but difficult to solve for me.
I have a Warehouse movements table with the movements of one year back (MOVEMENTS_1YB).
I need to make a cumulative sum of QTA_MOV_1YB grouped by CAUSAL_MOV_1YB,DATE_MOV_1YB and ITEM and ordered by DATE_MOV_1YB.
My goal is produce a field within the script called CUM_QTA_MOV_1YB.
I've attached the script that produce an error ('INVALID EXPRESSION') now.
MOVEMENTS_1YB:
LOAD
ITEM,
QTA_MOV_1YB,
DATE_MOV_1YB,
CAUSAL_MOV_1YB
RESIDENT WAREHOUSE_MOVEMENTS
WHERE DATE_MOV_1YB>=ADDMONTHS(DATE(TODAY()),-$(ROLLING_PERIOD));
CUMULATIVE_SUM:
LOAD
ITEM,
DATE_MOV_1YB,
CAUSAL_MOV_1YB,
RESIDENT MOVEMENTS_1YB
GROUP BY CAUSAL_MOV_1YB,DATE_MOV_1YB,ITEM;
DROP TABLE MOVEMENTS_1YB;
Anybody can help me?
Thanks in advance.
First, you can create a new table with the required grouping, and then reload it, accumulating data in the field
RangeSum (QTA_MOV_1YB, Peek ( 'CUM_QTA_MOV_1YB')), как CUM_QTA_MOV_1YB
Thank you Oleg, I'm confident that i'm near the solution but there are some problems yet.
This is my script with the peek and previous method:
WAREHOUSE_MOVEMENTS:
LOAD
ITEM,
DATE_MOV,
MONTH_MOV,
QTY_MOV,
CAUSAL_MOV
RESIDENT MOV_MAG
ORDER BY ITEM ASC,DATE_MOV ASC,CAUSAL_MOV ASC;
DROP TABLE MOV_MAG;
CUMULATIVE_SUM:
LOAD
ITEM,
IF((
Previous(CAUSAL_MOV) = CAUSAL_MOV and
Previous(DATE_MOV) = DATE_MOV and
Previous(ITEM) = ITEM
),
(peek('CUM_QTA_MOV_1YB')+QTY_MOV),
QTY_MOV) AS CUM_QTA_MOV_1YB
RESIDENT WAREHOUSE_MOVEMENTS;
Unfortunately the result isn't so good yet.
and so on.
I think that the key field ITEM isn't enough to linking tables.
I show you what i want to have like result.
.................................................................................................................................... and so on.
I've changed the key with a composite key but it hasn't a good idea.
Can you tell me what there is wrong??
Thank you in advance!!! I think i will buy your book.
Hi Michele,
A few comments:
1. Based on your desired output, it looks like you want to accumulate quantities just by Items, not by Item, Date, and Causal.
So, in this case, you should only compare Item with the Previous(Item).
2. Based on our desired output, you need to add the cumulative "running total" in the same table with the movements, not in a separate table by ITEM - that would produce wrong results.
So, I think your script should look like this:
WAREHOUSE_MOVEMENTS_TEMP:
LOAD
ITEM,
DATE_MOV,
MONTH_MOV,
QTY_MOV,
CAUSAL_MOV
RESIDENT
MOV_MAG
ORDER BY
ITEM ASC,DATE_MOV ASC,CAUSAL_MOV ASC;
DROP TABLE MOV_MAG;
WAREHOUSE_MOVEMENTS:
LOAD
ITEM,
DATE_MOV,
MONTH_MOV,
QTY_MOV,
IF((
Previous(ITEM) = ITEM
),
RangeSum(peek('CUM_QTA_MOV_1YB') , QTY_MOV),
QTY_MOV) AS CUM_QTA_MOV_1YB
RESIDENT
WAREHOUSE_MOVEMENTS_TEMP
ORDER BY
ITEM,
DATE_MOV,
CAUSAL_MOV
;
DROP TABLE WAREHOUSE_MOVEMENTS_TEMP;
I also replaced a simple addition ( the plus sign) with a RangeSum, to protect it against an accidental NULL value...
Now you better buy my book 🙂
cheers,
Oleg Troyansky
Well, now everything is perfect!!!!
I surf in internet to buy your book of course!!
I want to increase my development ability!!!
Hi Michele,
glad I could help. You can find the book here:
cheers,
Oleg Troyansky