Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
rasmusilestedt
New Contributor

Set analysis struggles: Rolling sales for previous month

Dear Qlik Community, let me start by asking you a question:

How hard can it be to see sales for PREVIOUS MONTH but in a rolling fashion?

Even though I am only a few weeks new to Qlik Sense, it sounds so simple to me. But I just can't manage to do it.

I have been pulling my hair for days now and decided to turn to this community -  let's see if we have anyone who can manage to figure this one out 😉

I want to have Net Sales (that is, in my case: Sum({$<ACCOUNT = {'Net sales - external customers'}>}Value) but for PREVIOUS MONTH rolling in a bar chart.

In other words, I don't want to see just the sales of previous month in one snap shot today, but I want it in a measure over time.

I have sales data on a Year-Month granuality where my primary key is called TIME_ID, which looks like this e.g. "YYYYMM".

I have set my date format in the script like: SET DateFormat='YYYYMM'.

Thereafter, I have several fields to that TIME_ID, some of which I have constructed only in order to try and solve my problem:

Examples of one row in my time-data:

(Key)TIME_ID = 201806

IS_Rolling_12_Month = -1 (i.e. True)

Year = 2018

Month = June

YearMonth = 2018 - June (this is the dimension I use in my charts)

DaysInMonth = 30

PreviousYearMonth = 2018 - May


Side note:

[As some of you might have guessed by now - the ULTIMATE goal is to add sales of the last two months and divide it by days of last two months in order to get "daily sales" e.g. net sales of two months divided by number of days in those two months, but let's not get ahead of ourselves.. I can't even get the two months sales straight yet...]

So, moving on. In the script, my key "TIME_ID" is loaded like this:

LOAD

Date#(Left(TIMEID,6),('YYYYMM')) as TIME_ID [Because original time_id was called TIMEID and had two zeros at the end which I removed)

YearMonth field is loaded like this:

LOAD

Year(Date#(Left(TIMEID,6),('YYYYMM')))&' - '& Month(Date#(Left(TIMEID,6),('YYYYMM'))) as YearMonth,

PreviousYearMonth field is loaded like this:

LOAD

Year(AddMonths(Date#(Left(TIMEID,6),('YYYYMM')),-1))&' - '& Month(AddMonths(Date#(Left(TIMEID,6),('YYYYMM')),-1)) as PreviousYearMonth,

What I am now struggling with is to have a selected period of time, e.g. 2018 - Feb displaying Net Sales for 2018 - Jan.

I have managed to create the fields and the data, but I don't know how to make the match work in a set analysis.

Who can possibly help me with this? I would be eternally grateful.

Cheers,
Rasmus