topic Re: Cumulative sum depends on dimension values in QlikView

Cumulative sum depends on dimension values

Sat, 18 Oct 2014 17:07:31 GMT

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

Re: Cumulative sum depends on dimension values

MK_QSL — Sat, 18 Oct 2014 18:29:50 GMT

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;

Re: Cumulative sum depends on dimension values

afurtado — Sat, 18 Oct 2014 18:34:40 GMT

Hi SwathiRa,

take a look in my model. I hope is what you need.

Best,

Alessandro Furtado

Re: Cumulative sum depends on dimension values

MarcoWedel — Sat, 18 Oct 2014 20:33:12 GMT

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

Re: Re: Cumulative sum depends on dimension values

Mon, 27 Oct 2014 15:40:38 GMT

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

Re: Cumulative sum depends on dimension values

maxgro — Mon, 27 Oct 2014 20:06:50 GMT

tmp:

load rowno() as id, *;

LOAD

sales

FROM [http://community.qlik.com/thread/138528] (html, codepage is 1252, embedded labels, table is @5);

table:

LOAD

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;

Re: Cumulative sum depends on dimension values

jagan — Tue, 28 Oct 2014 04:05:55 GMT

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.