cytd- lytd for any year selection

[ioannaiogr]

Hi all.

I'm looking to create a CYTD-LYTD KPI . Please correct my KPI

fabs(count({1}{< CYTDFlag={1},[Year]=, [Month]= >} ID])
-count({1}{<[Dates]={">=$(=YearStart(Max(date([Dates]),'DD/MM/YYYY'),-1))<=$(=AddYears(Max(date([Dates]),'DD/MM/YYYY'),-1))"},[Year]=, [Month]= >} Id)
)

because CYTD Dates are up to 1/7/2021

and the second part is doing the correct difference only if for 2021 year selection.

If I select 2020, it still keeps the count({1}{< CYTDFlag={1},[Year]=, [Month]= >} ID]) of 2021 and the second part is the whole year of 2019, BUT the difference i want to see  is 

if i select 2021 from the year filter to see in KPI

01/01/2021 to 01/07/2021 compared to 01/01/2020 to 01/07/2020

if i select 2020 from the year filter to see in kpi

01/01/2020  to 01/07/2020 compared to 01/01/2019  to 01/07/2019 etc.

Please help! Thanks

[rubenmarin]

Hi, to calculate the current year only use the same expression you used for lytd but removing the part where you substract one year, it could be:

fabs(count({1}{<[Dates]={">=$(=YearStart(Max(date([Dates]),'DD/MM/YYYY')))<=$(=date(Max([Dates])),'DD/MM/YYYY')"},[Year]=, [Month]= >} Id)
-count({1}{<[Dates]={">=$(=YearStart(Max(date([Dates]),'DD/MM/YYYY'),-1))<=$(=AddYears(Max(date([Dates]),'DD/MM/YYYY'),-1))"},[Year]=, [Month]= >} Id)
)

[ioannaiogr]

Hi @rubenmarin , for the first part expression [Dates]={">=$(=YearStart(Max(date([Dates]),'DD/MM/YYYY')))<=$(=date(Max([Dates])),'DD/MM/YYYY')"} i see in the message {">=1/1/2021<="}, i would like to see {">=1/1/2021<=1/7/2021"}. The second part is indeed {">=1/1/2020<=1/7/2020"}. Do you know how this could be solved?

[rubenmarin]

I trhink it's because of parenthesys, try:

 [Dates]={">=$(=YearStart(Max(date([Dates]),'DD/MM/YYYY')))<=$(=date(Max([Dates]),'DD/MM/YYYY'))"}

[ioannaiogr]

Now the first part is {">=1/1/2021<=-"} .Strange!!! @rubenmarin 

[rubenmarin]

Indeed it's strange, I don't see anything wrong, If you add =date(Max([Dates]),'DD/MM/YYYY') in any expression(table or text), it works?

There was another parenthesys issue in the first part, it should be:

 [Dates]={">=$(=YearStart(Max(date([Dates],'DD/MM/YYYY'))))<=$(=date(Max([Dates]),'DD/MM/YYYY'))"}

Or just:

 [Dates]={">=$(=YearStart(Max([Dates])))<=$(=date(Max([Dates]),'DD/MM/YYYY'))"}

[ioannaiogr]

Yes, =date(Max([Dates]),'DD/MM/YYYY')works fine in a table, shows 1/7/2021 as it should.

Ok now it works after i did  [Dates]={">=$(=YearStart(Max(date([Dates],'DD/MM/YYYY'))))<=$(=date(Max([Dates]),'DD/MM/YYYY'))"} as you suggested, but for selection of 2020 does a comparison of the range 01/01/2020 to 31/12/2020 vs 01/01/2019 to 31/12/2019, in fact i would like 01/01/2020 to 01/07/2020 vs 01/01/2019 to 01/07/2019. 

I appreciate your help @rubenmarin , really!

[rubenmarin · Accepted solution]

Ok, I missed that part, in that case to create the end date as the day 1 of the current month it could be:

<=$(=date(MakeDate(Year(Max([Dates])),Month(Today())),'DD/MM/YYYY'))"}

and for last year:

<=$(=date(MakeDate(Year(Max([Dates]))-1,Month(Today())),'DD/MM/YYYY'))"}

[ioannaiogr]

Ace! Thanks very much @rubenmarin  😄 😄 😄 

[ioannaiogr]

Hi, @rubenmarin  i need your help once again.

This cytd-lytd i want to show, I want it to go from the beginning of each year to the max(date) of event in the dataset.

For example

For 2021 i want to show 01/01/2021 to Today which is 12/7/2021 vs 01/01/2020  to Max(Today)-1 year(so in that case 12/07/2020)

For 2020 i want to show 01/01/2020 to Max(Today)-2 years (12/7/2020) vs 01/01/2019  to Max(Today)-3 years (so in that case 12/07/2019)

etc

Hope you can help 🙂 Thanks