Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have revenue data for multiple years - 2017, 2018, 2019, 2020, 2021.
I have a table that shows YTD revenue data for current year; i.e., revenue as of March 2021. I am trying to add a column that will show revenue as of previous year YTD; i.e., March 2020 YTD.
But I want this to be a dynamic field. So when we get to April, the 2020 revenue column should show April 2020 YTD.
Example:
As of March
2021 YTD | 2020 YTD | |
Revenue | 100,000 | 95,000 |
Now when we get to April (if we add $15k in April 2021 and had added $10k in April 2020)
2021 YTD | 2020 YTD | |
Revenue | 115,000 | 105,000 |
The QVD is connected to the database and refreshes every day. The fields being pulled are:
FiscalYr (2017, 2018, 2019, 2020, 2021)
FiscalPeriod (2017-01... 2017-12, 2018-01... 2018-12, 2019-01... 2019-12, 2020-01...2020-12, 2021-01...)
Revenue
Thank you for the help.
Try like below
SET DateFormat='YYYY-MM-DD';
Sample data:
Load *, Date(FiscalPeriod&'-01', 'YYYY-MM-DD') as FiscalDate Inline
[
FiscalYr,FiscalPeriod, Revenue
2020, 2020-01, 100
2020, 2020-02,200
2020, 2020-03,300
2020, 2020-04,400
2021, 2021-01, 1100
2021, 2021-02,1200
2021, 2021-03,1300
];
In Front end:
exp Label: =Year(AddYears(Max(FiscalDate),-1)) & ' YTD'
=Sum({<FiscalDate={">=$(=YearStart(Max(FiscalDate),-1))<=$(=AddYears(Max(FiscalDate),-1))"}>}Revenue)
exp Label: =Year(Max(FiscalDate))& ' YTD'
=Sum({<FiscalDate={">=$(=YearStart(Max(FiscalDate),0))<=$(=Max(FiscalDate))"}>}Revenue)
o/p:
You can change the sample data with your QVD.