Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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()
Do you have a date field (in addtion to YearQuarter) to restrict for the last 5 quarter?
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
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
Nope, I don't really understand. Can you elaborate with an example may be?
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?
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()