Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Opposite of cumulative sum?

  1. #1
    Join Date
    Dec 2016
    Posts
    27

    Default Opposite of cumulative sum?

    Hi,

    I'm working with some data which is cumulative. I have amounts by account and date which I can put in order, but for a given account, what is the cleanest way to subtract the value for date d-1 from the value for date d? Clearly for the earliest date there would be no prior date amount to subtract.

    Thanks,

    Andy

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You can't calculate a difference for the first value - that's it.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Dec 2016
    Posts
    27

    Default

    Quote Originally Posted by marabu View Post
    You can't calculate a difference for the first value - that's it.

    Sorry marabu

    I'm not sure I understand your reply. Is it the case that I cannot remove a rolling total/cumulative effect from a sequence of amounts?

    To recap on my ask, my data looks like this

    Date Account Amount
    31/01/2016 n123456 10
    31/03/2016 n123456 25
    31/08/2016 n123456 70
    30/09/2016 n123456 71
    29/02/2016 x987654 50
    31/03/2016 x987654 305
    30/04/2016 x987654 1000
    30/09/2016 x987654 5000

    What I would like to calculate is this:


    Date Account Amount
    31/01/2016 n123456 10
    31/03/2016 n123456 15
    31/08/2016 n123456 45
    30/09/2016 n123456 1
    29/02/2016 x987654 50
    31/03/2016 x987654 255
    30/04/2016 x987654 695
    30/09/2016 x987654 4000

    Is there a nice way for me to achieve this using kettle?

    Thanks,


    Andy

  4. #4
    Join Date
    Nov 2009
    Posts
    688

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Now I see. You have a report showing the cummulative sales volume (amount) per customer (account) and month (date) and need a report showing the sales volume per customer and month. In that case you will start with an implicit sales volume of zero.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Dec 2016
    Posts
    27

    Default

    This was incredibly helpful, thanks. I have what I consider to be a complex transformation working front to back now with all of your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.