Cumulated value

Unknown
Unknown Active Partner
in Platform

Hi,

 

I want to create a cumulated value in my Dataflow, not with a Yearly Cumulated Value, or with a Yearly Moving Total.

I think it is a basic function in particular for inventory but I don't find it.

 

Thanks

Answers

  • Unknown
    Unknown Active Partner

    Hi Sébastien LEGER,

      This sounds similar to Wahyu Nugraha's question about Summary All Data Until Date Selection. As I mentioned there, consider using the cycle property is a useful time function to control exactly how many prior periods are included in the sum. By default cycle=1. Cycle denotes how many periods are included in the sum, at the axes time grain. If we're looking at monthly data, with a cycle of 36, we will see a sum of the last 36 months of data.

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    250 Likes Fourth Anniversary 100 Up Votes 100 Comments

    Hi Sébastien LEGER,

    it's not as easy as it it may seem to cumulate values in a dataflow since dataflows always work in "replace"-mode. So you might consider extracting your data to an ASCII file and re-importing the values using the "blank" option as opposed to "replace"--this will add up.

    ...just an idea...

     

    Kind Regards,

    Helmut

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    Hi,

     

    is there a cetain reason why you can't use YCV? Do you want to make the cumulation not on time entity?

     

    Regards

    Björn

  • Unknown
    Unknown Active Partner

    Hi Björn,

     

    I need the cumulated value for all the life of my article, for a long time.

     

    Example:

    20/12/2013 120 0004 104 000
    21/12/20134 104 000
    22/12/20134 104 000
    23/12/2013 216 0004 320 000
    24/12/2013- 126 0004 194 000
    25/12/20134 194 000
    26/12/2013- 102 0004 092 000
    27/12/2013- 48 0004 044 000
    28/12/20134 044 000
    29/12/20134 044 000
    30/12/2013- 246 0003 798 000
    31/12/2013- 108 0003 690 000
    06/01/2014 552 000 552 000

    It is perfect for 2013 but after the calculation is new for the new year.

     

    So the simple "Accumulated Value" is OK for me.

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    Hi,

     

    than just use the ycv with a very high cycle value (9999), so the cumulation is only restarted after this number of time periods (details you can find in our manual: Time functions )

     

    Regards

    Björn

  • Unknown
    Unknown Active Partner
    edited March 2020

    Hi,

     

    I tried this solution but it doesn't work. I have the same result.

    image

    Regards,

    Sébastien.

  • Björn Reuber
    Björn Reuber Employee, Community Captain
    500 Likes Fourth Anniversary 100 Comments 5 Answers

    Hi,

     

    is your cube structured on Week level? Can you please try if its working with Month in by row instead of week?

     

    Regards

    Björn

  • Unknown
    Unknown Active Partner

    Hi,

     

    My cube is structured on day level.

    When I choose Month, it's the same result, with a cycle with 9999 value.

     

    Regards,

     

    Sébastien.

  • Unknown
    Unknown Active Partner

    Hi Björn,

     

    I tried with YMT(not YCV) and a cycle with 9999, and it works, but it is a long calculation : around 2 minutes 30 sec. with a Core I7 versus 15 seconds with YCV without cycle.

    I will search another solutions for my customer.

     

    Thanks a lot Björn,

     

    Regards,

     

    Sébastien.

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    Hi Sébastien LEGER

     

    I Also have some problem, that is solution is work but time execution long calculation.

    And I try the another solution, but it must create temp cube with procedure.

    So workaround, i cut off a transcational and make ending balance each year.

    Cumulated Previous Year transaction store to First day Next Year(temp cube)

    So If i want Show Stock per Year just "Sum temp cube" with yearly moving total + "selection Year transaction".

  • Brendan Broughton
    Brendan Broughton Active Partner
    Third Anniversary 100 Comments 100 Up Votes 25 Likes

    Hi Sebastien,

    I had to do this a while ago to chart a cummulated 'project to date' value which could go over multiple years.  I did it with a Nexel Writeback, and it was done 'on-the-go' to calculate for an individual project when the user requested the chart. 

     

    I'm not sure of your requirements but it is another option to possible look at.

     

    A warning though: Nexel writeback, even though it is called in a procedure, is only able to be run by a Lite+ user or above.

  • Etienne CAUSSE
    Etienne CAUSSE Customer
    Fourth Anniversary 250 Up Votes 100 Comments 100 Likes

    Hi Sébastien LEGER

    I noticed Yearly Moving Total and YCV functions are not very efficient when a 128-bit sparsity is used. This is supposed to be improved in Board 11 but in the mean time, I optimized some structures (more dense entities in some cases) to avoid 128-bit sparsity and as a result, the dataflows and layouts using Time cumulating functions are much faster.

Categories