Deriving a number from Month

[#1]

Hi all,

I have a small problem that I am sure has been solved on here already but despite numerous searches I cant find anything that works.

I have an Inputbox with a drop down list Jan; Feb; Mar; etc where the user selects the month and then loads the QVW,

I have the InputBox linked to a variable varMonth. I use the varmont variable to calculate totals of particular columns depending on the month chosen but this way the current column is always in the correct position. This works fine example formula sum(ACT_C_YTD_$(varMonth))

I am doing various other things with this which work however what I can get working is that I have a budget line and depending on which month is chosen I want to calculate nth/12^th of the budget column.

I created another variable, I have tried a number of ways but just can get the thing to work as follows:

SET MonthCount = Num(Month(=$(varMonth)),'00');

SET MonthCount = Num(Month($(varMonth)),'00');

I have also set the formula up in the Document Properties > Variables> Custom. But the value shows Num(Month(=Nov),'00') if the Month selected is Nov. What I really want is 11 to be returned so formula in budget column would be Sum(Budget)/12*MonthCount.

Hope this all makes sense and its a simple fix or I am doing something silly.

Thanks in advance

Alan

[hector · Accepted solution]

Hi,

if your Monthvariable is configured, you can do this

=num(month(date#('nov','MMM')))

and this will return 11

Rgds

[#3]

Be aware that with "SET" you are setting a textual value. If you want qlikview to calculate something for you you have to use "LET" instead.

[#4]

Thanks everyone,

Hector your method worked, Its now solved.

I used the following

Let

MonthCount = num(month(date#('$(varMonth)','MMM')));

Regards Alan

PS: now I can try and figure out how to get the previous month in text ('Oct') format from the MonthCount 🙂

[hector]

Hi, that is not so complicated just use addmonths()

num(month(AddMonths(date#('nov','MMM'),-1))) = 10

num(month(AddMonths(date#('jan','MMM'),-1))) = 12

text(month(AddMonths(date#('jan','MMM'),-1))) = 'dec'

text(month(AddMonths(date#('nov','MMM'),-1))) = 'oct'

Rgds