topic Re: Last 6 months as a dimension in QlikView

Last 6 months as a dimension

Fri, 27 Mar 2015 15:30:19 GMT

Guys,

I have a table with 18 months data. However, in pivot I need only last 6 months as a dimension. Any suggestions?

Re: Last 6 months as a dimension

Anonymous — Fri, 27 Mar 2015 16:04:53 GMT

Best choice is to create a dimension Last6Mos in the script, for example, you have table Calendar containing Date and MonthYr fields among others. In this case:

LEFT JOIN (Calendar) LOAD
MonthYr,
MonthYr as Last6Mos
RESIDENT Calendar
WHERE Date >= monthstart(today(),-6);

If you don't want to change the script, use calculated dimension in your chart:

aggr(only({<Date={">=date(monthstart(today(),-6))"}>} MonthYr),MonthYr)

Re: Last 6 months as a dimension

patroser — Fri, 27 Mar 2015 16:21:46 GMT

Not that beautiful but it works:

=If(Year(AddMonths(vDate, -6)) = Year(vDate),

If(Month > Month(AddMonths(vDate, -6)) and Month <= Month(vDate), Month),

If(Month > Month(AddMonths(vDate, -6)) or Month <= Month(vDate), Month))

where vDate could be your Today()

Best regards,

Patrick

Re: Last 6 months as a dimension

Mon, 30 Mar 2015 11:11:17 GMT

Hi, Michael,

I need this in pivot only, so I cant change th scipt. However, the formula that u wrote isn't working

Re: Last 6 months as a dimension

Anonymous — Mon, 30 Mar 2015 12:40:11 GMT

Hi Diana,

Still, the best option is to have an additional field for this. Not instead of the current Month field. So you can use new field in one chart and old field in another.

As for the "isn't working" - my example is a "generic" one, I don't know the specific names in your application nor you data model. I can be more specific if you upload your application.

Re: Last 6 months as a dimension

PrashantSangle — Mon, 30 Mar 2015 12:47:26 GMT

Hi,

What if instead of creating new field,

If i took Month as dimension and

in Expression if i restrict data for latest 6 month only

like Sum({<Month={">=$(=Month(AddMonths(max(Month),6)))<=$(=Month(max(Month)))"}>}Sales)

Regards

Re: Last 6 months as a dimension

Anonymous — Mon, 30 Mar 2015 12:54:58 GMT

It is fine to restrict on the expression level. I prefer calculated dimension for one reason only - if there are multiple expressions, there is no need to make this change in every one if you use calculated dimension.

Re: Last 6 months as a dimension

avinashelite — Mon, 30 Mar 2015 12:57:49 GMT

Hi Diana,

you can do it in two ways,

1.In the dimension add a calculate dimension using the if condition:

If(Month <= monthname(today()) and Month>=mothname(date_feild)-18, dimension_you_want)

2.using the set analysis:

sum({<Calender_Date={">=$(Month <= monthname(today()) and Month>=mothname(date_feild))-18))"}>}sales)

Re: Last 6 months as a dimension

PrashantSangle — Mon, 30 Mar 2015 13:12:41 GMT

Ok, Thanks for clearification.

It was just my doubt.

Regards