Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Last 6 months as a dimension

Guys,

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

Tags (1)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Last 6 months as a dimension

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)

View solution in original post

8 Replies
mov
Esteemed Contributor III

Re: Last 6 months as a dimension

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)

View solution in original post

Partner
Partner

Re: Last 6 months as a dimension

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

Not applicable

Re: Last 6 months as a dimension

Hi, Michael,

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

mov
Esteemed Contributor III

Re: Last 6 months as a dimension

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

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

Great dreamer's dreams never fulfilled, they are always transcended.
mov
Esteemed Contributor III

Re: Last 6 months as a dimension

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

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)

or

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

Ok, Thanks for clearification.

It was just my doubt.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.