topic Re: Set Analysis Expression for previous 3 months in QlikView

Set Analysis Expression for previous 3 months

Tue, 08 Apr 2014 07:37:19 GMT

Hi all,

I want to have set analysis expression for the following scenario.

We are now in the month of April,

I have to show now previous 3 months (Jan, Feb, Mar) Bookings.

when we move to May, automatically (Feb, Mar, Apr) should be displayed.

Hardcoding (sum ({<Year={'2014'}, Month={'Jan','Feb','Mar'}[Bookings]) ) is not allowed since i am looking expression for the generation of pdf report.

i tried the following expression

sum({<Year={'2014'},
[Date formatted]={">=$(AddMonths(YearStart($(vDate)),Month($(vDate))-4))"}* {"<=$(Date(MonthStart($(vDate))-1))"}}>}[Booking])

but i am getting YTD, which i am not looking for..

If i give "AddMonths(YearStart($(vDate)),Month($(vDate))-4))" in a text box I am getting Jan2014 and

if i give "Date(MonthStart($(vDate))-1))" am getting Mar 2014, which is what i want.

but when I combine them and give them as expression in a pivot table as shown above, I am getting YTD

I want to display the data from Jan 2014 to Mar 2014, since we are in April.

Hope my query is clear.

Kindly help me on this issue.

Thanks in advance

Vidhya

Re: Set Analysis Expression for previous 3 months

Tue, 08 Apr 2014 07:45:34 GMT

Hi vidhyavadkap,

You need to create 3 variables and put value in them like

VMonth1 = AddMonths(Today(),-3)

VMonth2 = AddMonths(Today(),-2)

VMonth3 = AddMonths(Today(),-1)

and use $(VMonth1) in your expression.

Re: Set Analysis Expression for previous 3 months

mato32188 — Tue, 08 Apr 2014 08:13:15 GMT

Hi,

create 2 variables:

vPrevMonth ; month(addmonths([Date field], -1))

vPrev3Months ; month(addmonths([Date field], -3))

use it in your expression calc by <=vPrevMonth; >=vPrev3Months

hope helps

Re: Set Analysis Expression for previous 3 months

Tue, 08 Apr 2014 11:35:06 GMT

Hi Ahmed,

I used Month(Addmonths(Today(),-3) in my expression and used 3 variables as suggested by you.

It worked and really helped me a lot.

Thanks,

Vidhya

Re: Set Analysis Expression for previous 3 months

Tue, 08 Apr 2014 12:11:57 GMT

Thank you it would be really great if my suggestion helps you.

if your requirement are completed then please mark my reply as Correct.

Re: Set Analysis Expression for previous 3 months

almamy_diaby — Tue, 08 Apr 2014 12:22:44 GMT

=sum({$< YEAR ={$(=Max(YEAR)),$(=Max(YEAR-1))},

MONTH={ "<=$(=num(date(AddMonths(max(MONTH_YEAR),0),'MMM')))"},

MONTH_YEAR={ ">=$(=num(date(AddMonths(max(MONTH_YEAR),-3),'MMM YYYY')))"}>} [Bookings])

*MONTH_YEAR = MonthName(DATE)