Last 6 months as a dimension

[#1]

Guys,

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

[Anonymous · Accepted solution]

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)

[patroser]

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

[#4]

Hi, Michael,

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

[Anonymous]

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.

[PrashantSangle]

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

[Anonymous]

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.

[avinashelite]

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)

[PrashantSangle]

Ok, Thanks for clearification.

It was just my doubt.

Regards