Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Last Three Months Calc

Hi,

I am creating a Pivot Table chart which will show certain Calculation the last three months.

This report will be static, say if we are in the Month of - FEB then the Report should show NOV-DEC-JAN

NovDecJan

In the next month ie. MAR the Report should show DEC-JAN-FEB.

DecJanFeb

The issue I am facing is with the Year, since it is 2014 and months needed in the report are of 2013 as well, I am stuck !

Please help me on the same.

Thanks.

1 Solution

Accepted Solutions
Highlighted
er_mohit
Honored Contributor II

Re: Last Three Months Calc

See the attached file

View solution in original post

14 Replies
Highlighted
Not applicable

Re: Last Three Months Calc

hi try this

sum({$<Month={'>=$(=max(Month-2))<=$(=max(Month))'}>}Value)

Highlighted
Not applicable

Re: Last Three Months Calc

hi

for year wise-

sum({$<Month={'>=$(=max(Month-2))<=$(=max(Month))'}, year={'=$(=max(year-1))'}>}Value)

Highlighted
Not applicable

Re: Last Three Months Calc

Hi Priyanka,

Use AddMonths function. Hope this is the right solution for your query.

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

Highlighted
Not applicable

Re: Last Three Months Calc

Egxample:

=Date#(month( addmonths ('2013-02-07',-3)),'DD/MM/YYYY')

Highlighted
er_mohit
Honored Contributor II

Re: Last Three Months Calc

Try this

sum({<Date={'>=$(=Monthstart(Max(Date),-3))<=$(=monthend(Max(Date),-1))'},YearField=,Monthfield=,QuarterField=>}SaleField)

or

Sum({<Date={'>=$(=Addmonth(Makedate(max(YearField),Max(Monthfield)),-3))'},YearFiled=,Monthfield=>}Amount)

hope it helps

Highlighted
Sokkorn
Honored Contributor

Re: Last Three Months Calc

Hi,

In the script, assign a consecutive number for each month, a unique ID. Here is an example

(Year(DateField) – 1)  * 12 + Num(Month(DateField)) as MonthID

In your expresion:

=Sum({1<MonthID = {">=$(=Max(MonthID)-3)<=$(=Max(MonthID))"},Month = ,Quarter = ,Year = >} Sales)

Regards,

Sokkorn

Highlighted
Partner
Partner

Re: Last Three Months Calc

sum({<MY ={">=$(=Date(addmonths(Max(MY), -2), 'MMM-YY')) <=$(=Date(addmonths(Max(MY), 0),  'MMM-YY'))"} >}

measure_field)

Highlighted
Not applicable

Re: Last Three Months Calc

Hi all,

Thanks for all your reply.

Each of your solution is pretty much near, but the resultant months I am getting is Oct-Nov-Dec, whereas my chart need to display Nov-Dec-Jan.

I think that is because of the change in Year Nov and dec are of 2013 and Jan is of 2014.

Kindly help me to solve this.

Thanks !

Highlighted
sundarakumar
Valued Contributor II

Re: Re: Last Three Months Calc

PFA. This should help,

I have added calculated dimension for this.

Hope this helps.

-sundar