Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have requirement where i need to accumulate the sum sales bases dimension.
any suggestions
here is the example:
input value:
=====
P | M | sales |
---|---|---|
A | 1 | 20 |
A | 1 | 25 |
A | 1 | 30 |
A | 2 | 50 |
A | 2 | 35 |
B | 1 | 25 |
B | 2 | 25 |
B | 3 | 29 |
C | 2 | 25 |
C | 2 | 90 |
Expected output
===========
P | M | Cumulative sales |
---|---|---|
A | 1 | 20 |
A | 1 | 45 |
A | 1 | 75 |
B | 1 | 25 |
B | 2 | 50 |
B | 3 | 79 |
C | 2 | 25 |
c | 2 | 115 |
Thanks
Hi,
I think you need to sort the data by P and M. Try this script below
Data:
LOAD [Sales document_DOC_NUMBER./BIC/AZSD_X7800] as docno,
[Item_S_ORD_ITEM./BIC/AZSD_X7800] as item,
[Plant_PLANT./BIC/AZSD_X7800] as P,
[Material_MATERIAL./BIC/AZSD_X7800] as M,
[Unavailable Quantity_/BIC/ZUNAV_QUA./BIC/AZSD_X7800] as qty
FROM
(qvd);
LOAD *,
If(AutoNumberHash128(P,M)=AutoNumberHash128(Peek('P'), Peek('M')), Peek([Cumulative qty])+qty,qty) as [Cumulative qty]
Resident Data
ORDER P, M;
DROP Table Data;
Hope this helps you.
Regards,
Jagan.
Temp:
Load * Inline
[
P, M, sales
A, 1, 20
A, 1, 25
A, 1, 30
A, 2, 50
A, 2, 35
B, 1, 25
B, 2, 25
B, 3, 29
C, 2, 25
C, 2, 90
];
NoConcatenate
Final:
Load
P,
M,
IF(P <> Previous(P), sales,
IF(P&M = 'A1', IF(P&M = Previous(P&M), RangeSum(sales, Peek('TotalSales'))),
IF(P = Previous(P) and P&M <> 'A1', RangeSum(sales, Peek('TotalSales'))))) as TotalSales
Resident Temp
Where P&M <> 'A2'
Order By P, M;
Drop Table Temp;
Hi SwathiRa,
take a look in my model. I hope is what you need.
Best,
Alessandro Furtado
Hi,
I'm not quite sure if you really meant this output table:
P | M | Cumulative sales |
---|---|---|
A | 1 | 20 |
A | 1 | 45 |
A | 1 | 75 |
B | 1 | 25 |
B | 2 | 50 |
B | 3 | 79 |
C | 2 | 25 |
c | 2 | 115 |
or this instead:
P | M | Cumulative sales |
---|---|---|
A | 1 | 20 |
A | 1 | 45 |
A | 1 | 75 |
A | 2 | 50 |
A | 2 | 85 |
B | 1 | 25 |
B | 1 | 50 |
B | 1 | 79 |
C | 2 | 25 |
C | 2 | 115 |
Supposed the latter, then this Input table
P | M | sales |
---|---|---|
A | 1 | 20 |
A | 1 | 25 |
A | 1 | 30 |
A | 2 | 50 |
A | 2 | 35 |
B | 1 | 25 |
B | 1 | 25 |
B | 1 | 29 |
C | 2 | 25 |
C | 2 | 90 |
could be transformed using this script:
LOAD P,
M,
sales,
If(AutoNumberHash128(P,M)=Previous(AutoNumberHash128(P,M)),Peek([Cumulative sales])+sales,sales) as [Cumulative sales]
FROM [http://community.qlik.com/thread/138528] (html, codepage is 1252, embedded labels, table is @1);
Output:
hope this helps
regards
Marco
Thank you Macro,
Yes I am looking for the same requirement what you were posted.
Looks your solution is works fine for some sample data but if we have millions of records in our application this accumulation is failing for particular 2 or 3 records.
I have attached qvw application where the cumulative value not happened.
Thanks
tmp:
load rowno() as id, *;
LOAD
P,
M,
sales
FROM [http://community.qlik.com/thread/138528] (html, codepage is 1252, embedded labels, table is @5);
table:
LOAD
P,
M,
sales,
If(peek(P)<>P or peek(M)<>M, sales,peek([Cumulative sales])+sales) as [Cumulative sales]
Resident tmp
order by id;
DROP table tmp;
Hi,
I think you need to sort the data by P and M. Try this script below
Data:
LOAD [Sales document_DOC_NUMBER./BIC/AZSD_X7800] as docno,
[Item_S_ORD_ITEM./BIC/AZSD_X7800] as item,
[Plant_PLANT./BIC/AZSD_X7800] as P,
[Material_MATERIAL./BIC/AZSD_X7800] as M,
[Unavailable Quantity_/BIC/ZUNAV_QUA./BIC/AZSD_X7800] as qty
FROM
(qvd);
LOAD *,
If(AutoNumberHash128(P,M)=AutoNumberHash128(Peek('P'), Peek('M')), Peek([Cumulative qty])+qty,qty) as [Cumulative qty]
Resident Data
ORDER P, M;
DROP Table Data;
Hope this helps you.
Regards,
Jagan.