Arranging yearmonth in descending order

[rsdhavle]

I have yearmonth values in my data in format 2013nov, 2013oct etc. I need to show latest 13 months in my chart. What is the proper way to do it?

[Gysbert_Wassenaar]

You could create your yearmonth field in the script like dual(yearmonth, date#(yearmonth,'YYYYMMM')) as yearmonth. That way it will actually be a date field with a display format of the original field. Since it's now a date field you can use the addmonths function to calculate the date 13 months earlier: if(yearmonth >= addmonths(yearmonth, -12), yearmonth)

[its_anandrjs]

I think you want a rolling months with latest month

[rsdhavle]

By using this function its not converting it to the date format. Its showing it in the same format as it was before.

[Gysbert_Wassenaar]

That's what I said: That way it will actually be a date field with a display format of the original field.

[rsdhavle]

Ok got it. I need to show the data of last 13 months in a bar chart and Yearmonth would be my dimension so lets say for latest 13 months of sales i need to show in bar chart. So how to restrict in bar chart that only 13 latest months are visible

[Gysbert_Wassenaar]

You can try using a set analysis expression. If your chart now has an expression sum(Sales) you would change it to sum({<yearmonth={'>=$(=addmonths(max(yearmonth),-12))'}>}Sales) to restrict it to the last 13 months.