Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| Country 1 | Line of Business 1 | Segment 1 | Type 1 | Yes | 1000 | 1000 |
| 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 |
| Country 1 | Line of Business 1 | Segment 1 | Type 1 | Yes | 1000 | Country 1Line of Business 1Segment 1Type1Yes201501 |
| 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!
I would try something like this: split the month and year, generate your unique key from the fields you need to and then only calculate the difference when the unique key is identical:
Load id, "Month", "year", "Business Unit", "Line of Business", Segment, "Type", "Yes/No Flag", Sales, if(id = Previous(id), Sales - Previous(Sales)) as SalesDiff; LOAD Hash128("Business Unit", "Line of Business", Segment, Type, "Yes/No Flag") as id, "Month", "year", "Business Unit", "Line of Business", Segment, "Type", "Yes/No Flag", Sales, if("Business Unit" = Previous("Business Unit"), Sales - Previous(Sales)) as SalesDiff FROM [lib://documents/data.xlsx] (ooxml, embedded labels, table is Tabelle1) ;