Display YTD value

[chiranjivdas]

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.

[MayilVahanan]

Hi @chiranjivdas 

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.