Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative sum depends on dimension values

Hi All,

I have requirement where i need to accumulate the  sum sales bases dimension.

any suggestions

here is the example:

input value:

=====

PMsales
A120
A125
A130
A250
A235
B125
B225
B329
C225
C290

Expected output

===========

PMCumulative sales
A120
A145
A175
B125
B250
B379
C225
c2115

Thanks

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
MK_QSL
MVP
MVP

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;

afurtado
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi SwathiRa,

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

Best,

Alessandro Furtado2014-10-18 15_33_04-QlikView x64 - [C__tmp_sum acum.qvw].png

furtado@farolbi.com.br
MarcoWedel

Hi,

I'm not quite sure if you really meant this output table:

PMCumulative sales
A120
A145
A175
B125
B250
B379
C225
c2115

or this instead:

PMCumulative sales
A120
A145
A175
A250
A285
B125
B150
B179
C225
C2115

Supposed the latter, then this Input table

PMsales
A120
A125
A130
A250
A235
B125
B125
B129
C225
C290

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:

QlikCommunity_Thread_138528_Pic1.JPG.jpg

hope this helps

regards

Marco

Not applicable
Author

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

maxgro
MVP
MVP

1.png

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;

jagan
Luminary Alumni
Luminary Alumni

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.