Set Analysis + previous month

[jakobjosef]

Hi dear Community,

does someone know how to show sales of the previous month?

In a text object, i would like to show how much sales increased compared to the month before.

So if my sales were like:

July: 100

August: 110

September: 150

Then my text object should show in September: 10 % (110/100 -1) (because sales increase from July to August for 10 %)

and in October: 36 % (150/110 -1)

Hope there is a simple solution, i could not manage to find a solution in different threads.

fyi: I know that "=Month(AddMonths(Today(),-1)) " Shows the last month, but how can I ad a  value?

Thank you very much for your help

Jakob

[marcus_sommer]

You could use something like this:

sum({< Month = {"$(=max(Month))"}>} Sales) / sum({< Month = {"$(=max(Month)-1)"}>} Sales)

and

sum({< Month = {"$(=max(Month)-1)"}>} Sales) / sum({< Month = {"$(=max(Month)-2)"}>} Sales)

Prerequisite for it is that Month is numeric - if not you shoud create such numeric month-field within your master-calendar.

- Marcus

[jakobjosef]

Hi Marcus,

thank you very much for your help and your answer.

Unfortunately it doesn't work yet and i dont know why. My month-field are numeric (for example if I create a text box with "=Month(today())-1", qlikView writes "8" which is right.

Maybe it is because of the fact that my master calender has maxDate 2020?
Just fyi - there are just sales until the current month, so regarding sales the max.month should be the actual one.

If it doesn't work because of maxDate 2020, is there a set analysis which works with the function "today()"?

Thank you very much

Jakob

[jakobjosef]

I also tried:

=Sum( { < Monat = {"Month(AddMonths(Today(),-1)) "} > } Sales)

(Monat = month)

"=month(AddMonths(Today(),-1))" as single expression in a text object works fine, QlikVeiw shows "Aug".

Greetings

Jakob

[timpoismans]

If you put a filter box on your view using the dimension Month, what does it show you? The 3 letter names of Months or numbers?

I mostly make use of variables in Set Analysis for previous months/years.

vPreviousMonth = Month(Today())-1

vPrevious2Month = Month(Today())-2

Sum({<Month={'$(=$(vPreviousMonth))'}>}Sales)/Sum({<Month={'$(=$(vPrevious2Month))'}>}Sales)

Though you should watchout with a measure like this, 'cause it's likely that it won't work in the first two months of the year.

Another thought is that this is all static, so it will only show the rise of last month compared to the second last month.

[sasiparupudi1]

Do you perhaps have a YYYYMM type field in the calendar?

[marcus_sommer]

With "=Month(today())-1" you are not testing your month-field. For the above mentioned approach you need a pure number. If for example your month-field is created with: month(DateField) it will probably (depending on your used standard-variables) return 'Jan' for the 01.01.2018 and if you check the numerical value of it you will get 1 and it is therefore a dual-value which has both a string-representation and numerical value.

But this is only applicable within a set analysis if you used >= or <= as comparing-method to enforce a numerical matching. By using just = the comparing will be applied on the string-representation and a match of 'Jan' = 1 will fail and this will happens if you applies any kind of calculation on your Month. Means if you calculate with the fieldvalue of 'Feb' - 1 you won't get 'Jan' else 1. Beside this you won't fetch with this approach a turn of the year.

The conclusion of it is that each kind of matching/calculating should be done only with pure numbers otherwise you will need more or less efforts to handle the formatting-stuff (means more efforts without any added value). The easiest way is mostly just to create all versions of your needed period-fields within the master-calendar like:

...

month(Date) as Month,

num(month(Date)) as MonthNum,

autonumber(year(Date) & month(Date)) as YearMonthContinuousNum, // by a sorted generation of the calendar

...

Then you could use the *Num fields within your calculations and even you used the field Month to select your values the numerical fields will work.

ps: the autonumber here is a possible turn of the year.

- Marcus

[jakobjosef]

Hi Tim,

thank you for your hint regarding the use of variables for this problem.

To answer your question:

if I create a text object wih "=month(AddMonths(Today(),-1))", then QlikView shows "Aug" (also when august is activated in month-listbox)

if I use the variable like you have shown me, QlikView shows "8".

I keep on trying - and I ll let you know it I got a solution, but thank you very much so far.

Jakob

[jakobjosef]

Yes I think so - so do I have to write "Num(...)" everythime I am using date, right?

[jakobjosef]

Hello Marcus,

thank you very much for your explaination, I am going to try to fix my Problem like you told me.

Also I know that there is still a lot to learn for me concerning QlikViews Set Analysis, but thank you very much so far, i really appreciate to get help in this community.

Jakob