topic Re: Show last 6 months results based on current selection in App Development

Show last 6 months results based on current selection

Anonymous — Wed, 21 Sep 2016 09:46:41 GMT

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.

Re: Show last 6 months results based on current selection

Anonymous — Wed, 21 Sep 2016 20:35:50 GMT

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.")

Re: Show last 6 months results based on current selection

Anonymous — Thu, 22 Sep 2016 12:19:04 GMT

Hi Michael,

thank you for your comment and help,

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

Re: Show last 6 months results based on current selection

Anonymous — Thu, 22 Sep 2016 15:22:46 GMT

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.