Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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/12th 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
Hi,
if your Monthvariable is configured, you can do this
=num(month(date#('nov','MMM')))
and this will return 11
Rgds
Hi,
if your Monthvariable is configured, you can do this
=num(month(date#('nov','MMM')))
and this will return 11
Rgds
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.
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 🙂
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