Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
achates2008
Contributor III
Contributor III

How to do with progressive calculation?

Hi there!

Can someone help me on this challenge?

Please see below example. First three columns are raw data. I want to progressively sum the amount of each item base on the date in ascending order to get the result as last column.

I am quite new in Qlikview, have no idea how to achieve this.

Thank you!

   

Itemdateamountprogressive sum per item
A1-Oct1010
A2-Oct1525
B3-Oct33
A4-Oct530
C5-Oct66
D6-Oct77
C7-Oct410
B8-Oct2326
A9-Oct838
1 Solution

Accepted Solutions
Gysbert_Wassenaar

// load the source data in a temporary table

Temp:

LOAD

     Item,

     date,

     amount

FROM

     sourcefile

     ;

// sort the data on Item first and date second using an order by

Result:

LOAD

     Item,

     date,

     amount,

/*  calculate the cumulative sum by checking if the item of the current record

     is the same as that of the previous record. If it's the same then get the current

     cumulative value from the target table using peek and add the current amount

     to it. If it isn't the store amount of the current record in the cumulative field.      */

     if(Previous(Item)=Item,

          rangesum(amount, peek([progressive sum per item])),

          amount) as [progressive sum per item]

RESIDENT

     Temp

ORDER BY

     Item, date

     ;

// drop the temporary table

DROP TABLE Temp;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

// load the source data in a temporary table

Temp:

LOAD

     Item,

     date,

     amount

FROM

     sourcefile

     ;

// sort the data on Item first and date second using an order by

Result:

LOAD

     Item,

     date,

     amount,

/*  calculate the cumulative sum by checking if the item of the current record

     is the same as that of the previous record. If it's the same then get the current

     cumulative value from the target table using peek and add the current amount

     to it. If it isn't the store amount of the current record in the cumulative field.      */

     if(Previous(Item)=Item,

          rangesum(amount, peek([progressive sum per item])),

          amount) as [progressive sum per item]

RESIDENT

     Temp

ORDER BY

     Item, date

     ;

// drop the temporary table

DROP TABLE Temp;


talk is cheap, supply exceeds demand

View solution in original post

achates2008
Contributor III
Contributor III
Author

Hi Gysbert!

Thank you so much for your quick answer! It solved my problem perfectly!

This is really an amazing community!