Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis Expression for previous 3 months


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


1 Solution

Accepted Solutions
Not applicable

Re: Set Analysis Expression for previous 3 months

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

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

5 Replies
Not applicable

Re: Set Analysis Expression for previous 3 months

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.

mato32188
Contributor III

Re: Set Analysis Expression for previous 3 months

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

Not applicable

Re: Set Analysis Expression for previous 3 months

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

Not applicable

Re: Set Analysis Expression for previous 3 months

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

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

almamy_diaby
Contributor

Re: Set Analysis Expression for previous 3 months

=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)

Community Browser