topic Re: Calculate Max month as a dimension? in QlikView

Calculate Max month as a dimension?

Wed, 22 Jan 2014 08:50:01 GMT

Hi All,

Actually i have 8 months, in this 7 months comes under 2013 and 1 month comes under 2014. so here max month should be 1st month

of 2014.How should i calculate max month as a dimension?.i used autonumber function on month&year but 1st month of 2014 giving 1 number so i am getting dec-2013 as a max month.i need jan-2014 as a max month.

plz help me on this.

Thanks,

Ashok.

Re: Calculate Max month as a dimension?

alexandros17 — Wed, 22 Jan 2014 08:53:05 GMT

Define month as concatenation of year and mont i.e. 201307, 201308 ... 201401, the sorting is automatic in this way and even the max

Hope it helps

Re: Calculate Max month as a dimension?

datanibbler — Wed, 22 Jan 2014 08:54:23 GMT

Hi Ashok,

instead of using the month such as it is, calculate MONTHEND() - that will give you Jan-31-2014 for this January, which should be your max_month, yes? Then calculate the max date out of these and then just plug in a MONTH() function.

Re: Calculate Max month as a dimension?

Wed, 22 Jan 2014 08:57:48 GMT

Try below expression.

=MonthName(Max(datefield)) //returns like Jan 2014

=Month(Max(datefield)) // returns like Jan

Regards,

Kabilan K.

Re: Calculate Max month as a dimension?

jvishnuram — Wed, 22 Jan 2014 09:00:50 GMT

Hi Ashok,

You can also use the MONTHID's instead of Month names like Max({$<MonthID={"$(=Max(MonthID))"}>}Month).

Re: Calculate Max month as a dimension?

Wed, 22 Jan 2014 09:16:32 GMT

Hi,

You can use calculated dimension as MonthName(Max(field)).

HTH

-shruti

Re: Calculate Max month as a dimension?

sudeepkm — Wed, 22 Jan 2014 09:53:14 GMT

I think you can use a Month Number. Suppose your date field name is Period then you can use the below expression in your Load Script to create a Month Number

num(Month(Period)) + if(Year(Period)=Year(Today()),12,0) as MonthNum,

and then you can use it in your chart expressions. Max(MonthNum)

Re: Calculate Max month as a dimension?

Wed, 22 Jan 2014 10:05:46 GMT

Thanks kabilan it working fine.