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
