Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
chiranjivdas
Contributor III
Contributor III

Display YTD value

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 YTD2020 YTD
Revenue100,00095,000

 

Now when we get to April (if we add $15k in April 2021 and had added $10k in April 2020)

 2021 YTD2020 YTD
Revenue115,000105,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.

Labels (1)
1 Reply
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:

MayilVahanan_0-1616472996652.png

You can change the sample data with your QVD.

Thanks & Regards,
Mayil Vahanan R