Show last 6 months results based on current selection

[Anonymous]

Hi there,

I want to display the last 6 months number of deliveries in a straight table.

e.g. I have currently selected AUGUST 2016 - It should show me the selected month, and the 5 months previous to that with it.

Month           Num Del.  

February	400
March	630
April	700
May	340
June	920
July	778
August	1000

If the selected month is February For example, it should be able to look back to the previous year also.

e.g.

September		200
October		989
November		223
December		967
January		443
February		400

As the examples show, the number of deliveries is the number recorded for each month, not accumulative.

Thanks for any help.

[Anonymous]

You need Month as a date field, or even better to have "daily" date field.  Now you can use calculated dimension:

=aggr(only({<Date={"$(='<=' & date(monthend(max(Month))) & '>=' & date(monthstart(addmonths(max(Month),-5))))"},Month=>} Month),Month)

I'm using max() here in case more than one Month is selected, or no selection.

You have also to uncheck "Show Null Values".

Chart expression will be your expression but ignoring Month selection, e.g.:

sum({<Month=>} "Num Del.")

[Anonymous]

Hi Michael,

thank you for your comment and help,

However, When I tried this, the calculated dimension field displayed nothing in the table?

[Anonymous · Accepted solution]

I got it to work!

I used 'MonthYear' as the dimension show it would order properly if it was looking back a year from the currently selected date.

I then used the following as the measure:

count({1<Date={'>=$(=AddMonths(Max(Date),-5)) <=$(=(Max(Date)))'}>}NumDeliveries)

This means that it will ignore the filter when it says look at a specific month, and instead only show when it is the current month and the previous 5 months.

Therefore showing a dynamic 6 month period.

Hope this helps others.