Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mattquinnterex
Contributor II

Show last 6 months results based on current selection

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
March630
April700
May340
June920
July778
August1000

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

e.g.

September200
October989
November223
December967
January443
February400

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

Thanks for any help.

1 Solution

Accepted Solutions
mattquinnterex
Contributor II

Re: Show last 6 months results based on current selection

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.

3 Replies
mov
Esteemed Contributor III

Re: Show last 6 months results based on current selection

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

mattquinnterex
Contributor II

Re: Show last 6 months results based on current selection

Hi Michael,

thank you for your comment and help,

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

mattquinnterex
Contributor II

Re: Show last 6 months results based on current selection

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.

Community Browser