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

[Anonymous]

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
01.2015	Country 1	Line of Business 1	Segment 1	Type 1	Yes	1000	1000
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
01.2015	Country 1	Line of Business 1	Segment 1	Type 1	Yes	1000	Country 1Line of Business 1Segment 1Type1Yes201501
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!

[Anonymous]

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)
;