topic Last N month values in QlikView

Last N month values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am looking for last N months Avg values in the chart. I am using below expression

=AVG({<Date ={">=$(=Date(addmonths(Max('$(vyearmonth)'), -2), 'MMM-YYYY')) <=$(=Date(addmonths(Max('$(vyearmonth)'), -1), 'MMM-YYYY'))"} >} Sales)

which is not giving any values

My data looks like below

load * Inline

[

Date,Sales

01/05/2016,20

02/05/2016,10

03/05/2016,20

04/05/2016,20

05/05/2016,30

.........];

vyearmonth

=MONTHNAME(date#(Date,'DD/MM/YYYY'))

Kindly let me know what and where I am missing

Re: Last N month values

sunny_talwar — Tue, 23 Aug 2016 13:10:29 GMT

What about this:

=Avg({<Date ={">=$(=Date(AddMonths(Max(Date), -2), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max(Date), -1), 'DD/MM/YYYY'))"}>} Sales)

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:13:08 GMT

Nope,

It is not working

Re: Last N month values

sunny_talwar — Tue, 23 Aug 2016 13:15:38 GMT

Have you made all these changes?

=Avg({<Date ={">=$(=Date(AddMonths(Max(Date), -2), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max(Date), -1), 'DD/MM/YYYY'))"}>} Sales)

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:22:08 GMT

Yeah, It is not working.

Re: Last N month values

sunny_talwar — Tue, 23 Aug 2016 13:23:46 GMT

Where are you using this expression and what is the expected output (based on your sample data)?

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:25:54 GMT

Hi,

I have to get last 3 or N months Avg value only.

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:31:05 GMT

I am hoping vyearmonth is a FIELD ?

how do you store the 'vyearmonth' ?

vyearmonth

=MONTHNAME(date#(Date,'DD/MM/YYYY'))

1)check your vyearmonth field's format MMM YYYY or MMM-YYYY

and try without ' ' for the field vyearmonth

2)=AVG({<Date ={">=$(=Date(addmonths(Max((vyearmonth)), -2), 'MMM-YYYY')) <=$(=Date(addmonths(Max((vyearmonth)), -1), 'MMM-YYYY'))"} >} Sales)

Re: Last N month values

stigchel — Tue, 23 Aug 2016 13:38:11 GMT

Are you sure that the field Date is an actual date formatted field? From your example load script this is not clear. To make sure, check if your set statements at the top of the load script contains this

SET DateFormat='DD/MM/YYYY';

or use

Load date#(Date,'DD/MM/YYYY') as Date,Sales;

load * Inline

[

PreDate,Sales

01/05/2016,20

02/05/2016,10

03/05/2016,20

04/05/2016,20

05/05/2016,30

.........];

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:41:35 GMT

Hi Allu,

vyearmonth is variable I was using in the pivot table.

As I was using in the Inline I have used Date# in the variable to get date format. I have tried your option 2 but still I am not getting any values.

Re: Last N month values

pgrenier — Tue, 23 Aug 2016 13:43:24 GMT

Good day,

I would recommend you to add a new MonthSeq field using AutoNumber() during the creation of your master calendar which would increment by 1 for each of the months your calendar contains starting with the current month and going backwards.

You could then use this sequential numeric field in order to get the last N months as such

AVG({<MonthSeq ={">($(vNbMonths)+1)"} >} Sales)

Regards,

Philippe

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:44:48 GMT

Hi Piet,

My original file have the date format. Also I have formatted the date in the variable. But still I am not getting the values.

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:48:22 GMT

Hi Philippe,

Thank you for your response. Actually I should not touch the existing script.

Re: Last N month values

Anil_Babu_Samineni — Tue, 23 Aug 2016 13:49:24 GMT

Can you post full expression, don't use expression or please provide variable expression which you used

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 13:54:17 GMT

Hi Anil,

I have posted my original post.

The expression I am using :

=AVG({<Date ={">=$(=Date(addmonths(Max('$(vyearmonth)'), -2), 'MMM-YYYY')) <=$(=Date(addmonths(Max('$(vyearmonth)'), -1), 'MMM-YYYY'))"} >} Sales)

Variable I am using :

vyearmonth

=MONTHNAME(date#(Date,'DD/MM/YYYY'))

My data goes like

load * Inline

[

Date,Sales

01/05/2016,20

02/05/2016,10

03/05/2016,20

04/05/2016,20

05/05/2016,30

.........];

Re: Last N month values

stigchel — Tue, 23 Aug 2016 14:07:02 GMT

Yes but you must use the expression Sunny suggested, what do you expect to be the result of

Max(MONTHNAME(date#(Date,'DD/MM/YYYY')))

Which results from your Max('$(vyearmonth)')

In these cases it is always good to use e.g a straight table where you use the expression but leave the label of the expression blank. The label will then show the result of $ sign expansions so you can check

Re: Last N month values

stigchel — Tue, 23 Aug 2016 14:07:48 GMT

And the document attached

Re: Last N month values

Anonymous — Tue, 23 Aug 2016 14:20:27 GMT

Hi Piet,

I was expecting last N month, for Ex ,in this case even though I am having 3 months of data I need to get last 2 months only in the pivot table.

Re: Last N month values

stigchel — Tue, 23 Aug 2016 14:41:25 GMT

?? Yes ok, but as you can see in my test document the expression is restricting the outcome to specific dates. So what is your question? If you need complete months you can add monthstart to the expression:

=Avg({<Date ={">=$(=Date(AddMonths(MonthStart(Max(Date)), -2), 'DD/MM/YYYY'))<$(=Date(MonthStart(Max(Date)), 'DD/MM/YYYY'))"}>} Sales)

The result in my example:

Re: Last N month values

othniel2014 — Tue, 23 Aug 2016 16:05:25 GMT

Hi, AVG Sales of the last 3 Months

Script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YY';

SET TimestampFormat='DD/MM/YY h:mm:ss[.fff] TT';

SET MonthNames='Ene.;Feb.;Mar.;Abr.;May.;Jun.;Jul.;Ago.;Sept.;Oct.;Nov.;Dic.';

SET DayNames='lun.;mar.;mié.;jue.;vie.;sáb.;dom.';

//Change Date <----field for Date_

Table:

LOAD * INLINE [

Date_, Sales

04/01/16, 26

06/01/16, 17

08/01/16, 19

......................

];

//add field --> NumDate

Join(Table)load

Date_,

Num(Date_) as NumDate,

Month(Date_)as Month_

Resident Table;

Graphic:

//without dimension, only expression...

//Edit: but you can add this calculated dimension

Dimension:

=Month($(=max(NumDate)))

Expression:

AVG({1<

NumDate={$(='">= ' & $(=Num(MonthStart(AddMonths(min(Num(Date_)),-2)))) & ' <= ' & $(=floor(num(max(Date_)))) &'"')}

>} Sales)