0 Replies Latest reply: Oct 12, 2018 11:50 AM by Nikita Pavlov RSS

    For each unique string deduct previous months value from current months value

    Nikita Pavlov

      Hi everyone,

       

      This is the first time I am asking a question in a forum, so please be kind!

       

      I have a flat Excel file dataset where several columns identify elements of a dimension and others contain measures. The measures are cumulative and it looks something like this:

       

      MonthYear (date field)

      Business Unit

      Line of Business

      Segment

      Type

      Yes/No Flag

      Sales

      01.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      1000

      02.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      2000

      01.2015

      Country 2

      Line of Business 2

      Segment 3

      Type 1

      No

      1000

      01.2015

      Country 3

      Line of Business 1

      Segment 1

      Type 1

      Yes

      10000

      02.2015

      Country 3

      Line of Business 1

      Segment 1

      Type 1

      Yes

      25000

       

       

      I would like to be able to create a new column - whether in the source table or in a visualisation - that shows the incremental variance for the same entity (combination of Business Unit - Line of Business - Segment - Type - Yes/No Flag) for each month and also to show the same value for January:

      MonthYear (date field)

      Business Unit

      Line of Business

      Segment

      Type

      Yes/No Flag

      Sales

      SalesVar

      1. 01.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      1000

      1000

      1. 02.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      2000

      1000

       

      Basically I would like to decumulate the cumulative sum that I have.

       

      I started by creating an ID field in the source file - a concatenated field that combines all the dimension elements plus year and month. It looks like this:

       

      MonthYear (date field)

      Business Unit

      Line of Business

      Segment

      Type

      Yes/No Flag

      Sales

      ID

      1. 01.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      1000

      Country 1Line of Business 1Segment 1Type1Yes201501

      1. 02.2015

      Country 1

      Line of Business 1

      Segment 1

      Type 1

      Yes

      2000

      Country 1Line of Business 1Segment 1Type1Yes201502

       

      If I sort this column in the Data Manager I can see that each of those IDs gets ordered together, i.e. for the same combination of Country, Line of Business, etc. and it does so from Jan to Dec. I thought this can help me create a new calculated column which would calculate this SalesVar for each of those unique IDs between each month but I am not sure how to do it.

       

      I tried loading script steps with Peek and Previous functions like this:

       

      Load   

      ([Sales] - Previous([Sales]) )as SalesVar,

        

      Resident Table1;

       

      I also tried to use time functions in the chart like this:

       

      if([MonthYear.autoCalendar.Month]=1,Sum([Sales]),

      Sum({$<[MonthYear.autoCalendar.Year]={$(vPriorMonthYear)},[MonthYear.autoCalendar.Month]={$(vPriorMonth)}>}[Sales]))

       

      But none of it worked!

       

      Any help will be very much appreciated!