topic Re: Last Three Months Calc in QlikView

Last Three Months Calc

Fri, 07 Feb 2014 06:11:38 GMT

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

Nov	Dec	Jan

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

Dec	Jan	Feb

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.

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:15:58 GMT

hi try this

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

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:20:01 GMT

for year wise-

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

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:20:08 GMT

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'

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:24:40 GMT

Egxample:

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

Re: Last Three Months Calc

er_mohit — Fri, 07 Feb 2014 06:33:09 GMT

Try this

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

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

hope it helps

Re: Last Three Months Calc

Sokkorn — Fri, 07 Feb 2014 06:40:59 GMT

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

Re: Last Three Months Calc

manojkulkarni — Fri, 07 Feb 2014 06:41:43 GMT

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

measure_field)

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:43:53 GMT

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 !

Re: Re: Last Three Months Calc

sundarakumar — Fri, 07 Feb 2014 06:47:53 GMT

PFA. This should help,

I have added calculated dimension for this.

Hope this helps.

-sundar

Re: Last Three Months Calc

Fri, 07 Feb 2014 06:54:44 GMT

=sum({<DateField={'>=$(Vrolling3)<=$(vMaxDate)'},monthfield=,yearfield=>}Amount)

Variables:

Vrolling3 : =Date(MonthStart(vMaxDate,-2),'DD/MM/YYYY')

vMaxDate : =Date(max(Date_Field),'DD/MM/YYYY')

Re: Last Three Months Calc

er_mohit — Fri, 07 Feb 2014 06:57:29 GMT

See the attached file

Re: Last Three Months Calc

Fri, 07 Feb 2014 07:06:25 GMT

=(Count({<[Year ]={$(VarPrevMonthFinYear)},[ Month]={$(VarPrevFinMonth)}>}MeasureField)

Below is Variables description:

VarPrevFinMonth=Month(AddMonths(VarFinMaxDate,-1))

VarFinMaxDate=day(max({<[Year ]={$(VarFinYear)},[MonthNum]={'$(VarFinMaxMonth)'}>}[Date]))

VarFinYear=max([Year])

VarFinMaxMonth=MAX({<[Year]={$(VarFinYear)}>} [MonthNum])

Hope it Helps you

Regards,

Divya

Re: Last Three Months Calc

kiranmanoharrode — Fri, 07 Feb 2014 07:18:35 GMT

Hello Priyanka,

You must have Month and MonthNum field in your table

For Ex. Month ='Jan' ; MonthNum= '1' ; Month='Dec'; MonthNum='12';

add calculated Dimension in your chart for displaying Month

=if(Today()=Today(), >= Month(addMonths(Today(),-3)) and <= Month(addmonths(Today(),-1)))

Regards

Kiran Rode

8976977897

Re: Last Three Months Calc

MK_QSL — Fri, 07 Feb 2014 07:53:44 GMT

Create a Pivot Table/Straight Table...

Dimensions As per your requirements.

Considering that your Month are like 'Jan', 'Feb' etc...

Current Month

Label : =Month(Today())

Expression : SUM({<Month = {'$(=Month(Today()))'}>}Sales)

Previous Month

Label : =Date(AddMonths(Today(),-1),'MMM')

Expression : SUM({<Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Sales)

2Months Back

Label : =Date(AddMonths(Today(),-2),'MMM')

Expression : SUM({<Month = {"$(=Date(AddMonths(Today(),-2),'MMM'))"}>}Sales)

And SO ON... Hope This helps..

If you want to compare as MonthNumber... you can simply use

MonthNo = {"$(=AddMonths(Today(),-1))"}

Instead of

Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}