What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Cummulative Measure as PDF --


Calculating a cummulative measure is common query in DW, but there is no straight forward way in DI. Either you have to write a custom function or you have to design using query transform.

I have a source data like this,

Run_TOT Source

I want target data like this,

Run_TOT Target

Well will see the design, here i have two QRY instances and generating a row number in each instance

Run_tot Row_num

will do a self join on the basis of RNUM and then summing up the credit value

Run_tot Where

and grouping the values based on Year & Month in the same QRY instance

Run_tot Group by

and then apply order by on the basis of Year and Credit in another QRY instance

Run_tot Order by

Execute the job, you’ll get the desired output.

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

2 comments on “Cummulative Measure

  1. Thanks Roger for your suggestion. Yes I could use that, but previous_row_value() function is there from 11.7 on wards. What if i’m using prior versions of DI like 11.5/6.x ? That’s why I’ve implemented in this way.

    Thanks,
    -Sudheer

Leave a Reply to Roger Cancel reply

Your email address will not be published. Required fields are marked *


*