Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
nikitapavlov
Contributor
Contributor

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

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!

1 Reply
Anonymous
Not applicable

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

qlik table.jpg