topic Getting previous month data in qlikview in QlikView

Getting previous month data in qlikview

Sat, 19 Jan 2013 15:14:59 GMT

attached is my qvw file. In management sheet there is a table called "Table of all questions (previos month) "

In this table i need to do same calculation but for the previous date.

One more thing, how can i place a title for the percentage column

Re: Getting previous month data in qlikview

Sat, 19 Jan 2013 18:23:44 GMT

Hi,

To get the analysis for Current Month and Previous Month, you can define two variables in Variable Overview (Ctrl+Alt+V)

1. vCurrentMonth

Definition: =Month(Today())

2. vPreviousMonth

Definition: =Month(AddMonths(Date(Today()),-1))

And modify your expressions to something like this:

=num(avg({<Month={$(vPreviousMonth)}>}MeanTotal)/100,'##%') for Previous Month

=num(avg({<Month={$(vCurrentMonth)}>}MeanTotal)/100,'##%') for Current Month

Please find attachment.

Hope it helps.

Regards,

-Khaled.

Re: Getting previous month data in qlikview

Sat, 19 Jan 2013 20:18:59 GMT

Thnx for the reply, but this doesnt work if you select a date.

Any other ideas. I want the values to be shown even i select jan 2012, not without selecting any date

Re: Getting previous month data in qlikview

Sat, 19 Jan 2013 20:56:21 GMT

Hi,

You can add a field to your calendar script as num(Month(Month)) as Month_Num and replace Month in the table expressions with Month_Num.

Modify the definitions for variables as follows:

1. vCurrentMonth

Definition: =num(Month)

2. vPreviousMonth

Definition: =if(num(Month)=1,12,num(Month)-1)

Use something like this expression for Current month

=num(avg({<Month_Num={$(vCurrentMonth)}>}MeanTotal)/100,'##%')

Use something like this expression for Previous Month

=num(avg({<Month_Num={$(vPreviousMonth)}>}MeanTotal)/100,'##%')

Hope it helps.

Regards,

Khaled.

Re: Getting previous month data in qlikview

Sat, 19 Jan 2013 21:03:55 GMT

use : Date(addmonths(Max(MonthName), -1),'MMM YYYY')

as ur vPreviousMonth variable value

and modify ur expression for Previous Month to:

num(avg({<Month={"=$(vPreviousMonth)"}>}MeanTotal)/100,'##%')

Re: Getting previous month data in qlikview

Sun, 20 Jan 2013 15:11:23 GMT

I used the following formula to get previous month

=MonthName(MonthName-1) which give Nov 2012 when i select Dec 2012

but when i tried the formula

num(avg({<MonthName={"=$(PrevMonth)"}>} MeanTotal)/100,'##%') it give me '-'

any other ideas???!!!!!

Re: Getting previous month data in qlikview

Sun, 20 Jan 2013 17:36:23 GMT

did u change the value of variable as i mentioned & then use this expression ?

Re: Getting previous month data in qlikview

adarsh_a — Tue, 01 Apr 2014 06:41:08 GMT

Hi,

Its better to extract month and year separately and apply the expression.

num(Month(Date)) as Cal_Month,
Year(Date) as Cal_Year,

then apply the above expression, it works