topic Re: Set Analysis + previous month in QlikView

Set Analysis + previous month

jakobjosef — Wed, 26 Sep 2018 14:28:38 GMT

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

Re: Set Analysis + previous month

marcus_sommer — Wed, 26 Sep 2018 14:48:18 GMT

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

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 10:52:24 GMT

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

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 11:06:58 GMT

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

Re: Set Analysis + previous month

timpoismans — Thu, 27 Sep 2018 11:18:19 GMT

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.

Re: Set Analysis + previous month

sasiparupudi1 — Thu, 27 Sep 2018 11:24:29 GMT

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

Re: Set Analysis + previous month

marcus_sommer — Thu, 27 Sep 2018 11:33:25 GMT

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

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 11:33:58 GMT

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

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 11:34:54 GMT

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

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 11:45:03 GMT

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

Re: Set Analysis + previous month

timpoismans — Thu, 27 Sep 2018 11:50:28 GMT

Adding to this, as Marcus suggested, in your Master Calendar table, I'd suggest you make a Month field, which contains the month as text, and a MonthNum field, which contains the month as number.

It's easier to compare to a number than text

If I may ask, why would you want to show the growth in a text object and not a table?

And how many months do you want to show?

What's your expected output?

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 12:17:14 GMT

Ok good to know, thank you

And of course you can ask -

This text object is just for a very quick summary - to see the growth of sales in %, compared to the month before. A table would be too much Information - as everyone says - keep it short and simple.

My expected Output is a number in % which shows the growth-rate.

If there is a easier solution, please tell me

Greetings

Jakob

Re: Set Analysis + previous month

timpoismans — Thu, 27 Sep 2018 12:21:00 GMT

My expected Output is a number in % which shows the growth-rate.

So you want to show the growth rate based on the two previous months in the current month?

So today, you would show the growth rate from August vs July.

Next week, in October, you would show the growth rate from September vs August?

Just that one value?

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 12:22:26 GMT

exactly!

Re: Set Analysis + previous month

timpoismans — Thu, 27 Sep 2018 12:26:25 GMT

And how would you handle the growth rate for a new year? Do you check the growth rate of December vs November in January?

Re: Set Analysis + previous month

jakobjosef — Thu, 27 Sep 2018 12:30:12 GMT

yes.

I already thought that this would cause technical challenges, but if it would not work for 1 month it wouldnt be a major problem.

Re: Set Analysis + previous month

timpoismans — Thu, 27 Sep 2018 13:01:54 GMT

This might be a bit too much for the purpose of it, but try the following.

vPreviousMonth = If(Month(Today())=1,12,Month(Today())-1)

vPrevious2Month = If(Month(Today())=1,11,If(Month(Today())=2,12,Month(Today())-2))

vCalcYear = If(Month(Today())=1,Year(Today())-1,Year(Today()))

vCalc2Year = If(Month(Today())=1 OR Month(Today())=2, Year(Today())-1,Year(Today()))

And then the measure:

Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)

This would just continue comparing the two previous months over the years.

But to be really thorough, you'd have to do the following:

If(Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

>= Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales),

(Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)),

-1+ (Sum({<Month={'$(=$(vPreviousMonth))'},Year={'$(=$(vCalcYear))'}>}Sales)

Sum({<Month={'$(=$(vPrevious2Month))'},Year={'$(=$(vCalc2Year))'}>}Sales)))

This would give you a positive percentage if your growth rate is >= 0 else it would give you a negative percentage, because you have dropped a certain percentage compared to the month before.