Show last 5 values in a chart

[Kunkulis]

Hello,

I to create a bar chart where on X-Axis I have quarter date written as YYYYQN (i.e. 2019Q1)
Using the Dimension Limits to show first 5 and reversing the order via Sort table give the information I want to see, but it show it in wrong order - current quarter is show on the left, but should be on the right. 

The last option is to get it via Expressions, currently this returns the correct data, but it has no limitation on the last 5 quarters:

SUM({<Event={'Criteria1', 'Criteria2', 'Criteria3', 'Criteria4'}, CCY= %Currency>}
ExRate * [AMOUNT])/1000000

So I get chart to show information from 2006Q1

The Quarter column name is 'YearQuarter'

EDIT:
The items in YearQuarter are ordered in ascending order - starts with the oldest 2006Q1 and ends with 2019Q1

[sunny_talwar]

Do you have a date field (in addtion to YearQuarter) to restrict for the last 5 quarter?

[Kunkulis]

In the table where we are linked to dates, we do have several of them.

%EffectiveFromDate_Key
YearMonth
Date
WorkingDay
EndMonth
Month
Year
Week
Day
YearQuarter

[sunny_talwar · Accepted solution]

Then use Date field set analysis to do this

Sum({<Event = {'Criteria1', 'Criteria2', 'Criteria3', 'Criteria4'},
      CCY= %Currency,
      Date = {"$(='>=' & Date(QuarterStart(Max(Date), -5), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>}
ExRate * [AMOUNT])/1000000

[Kunkulis]

The solution does decrease the amount of Quarters I see, thank you.
I had a solution similar to this, but using endmonths and just having -13, but the issue with this one was, that the the sum would be wrong each month, since new values would be added to current quarter each month, but for the last quarter, each month they would reduce until it goes on to the next quarter. I hope you understood what I meant

[sunny_talwar]

Nope, I don't really understand. Can you elaborate with an example may be?

[Kunkulis]

This was the date range at the beginning I used: 

{$<(EndMonth)={"$(='>='&AddMonths(MonthEnd(today((EndMonth))),-13)&'<='&MonthEnd(today()-1))"}

I used EndMonth because for all the new entries next to the registration date they show monthend date, so it felt a bit safer

2018-01	2018-02	2018-03	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09	2018-10	2018-11	2018-12	2019-01
2018Q1	2018Q1	2018Q1	2018Q2	2018Q2	2018Q2	2018Q3	2018Q3	2018Q3	2018Q4	2018Q4	2018Q4	2019Q1

In the table above, you see the YYYY-MM and to which quarter it goes.

So my concern is that in the formula I used at first it would add now 2019-02 to 2019Q1, but it would remove the 2018-01 from 2018Q1, thus 2018Q1 would only have data for the 2018-02 and 2018-03.

If it is still unclear, maybe you can explain how your formula works? 

[sunny_talwar · Accepted solution]

QuarterStart takes you to the beginning of the Quarter and that is why you should use QuarterStart instead of MonthStart or AddMonths. Give the expression I gave a shot and see what you get.

Also, you can test this expression in a text box object

='>=' & Date(QuarterStart(Today(), -5)) & '<=' & Date(Today())

to see what range will be used based on Today()

[Kunkulis]

Thank you for your help