Yearl Comparisson by using Moving Average

[SIDOC1]

Hello everybody,

I have created a line chart which should show comparisson between 2023 2022 as a rolling average in percentage. I have used the following formula for it:

(Count(distinct {<[dossierSubmitDate.autoCalendar.Year]={$(=(year(today())))}>}key_dossier)
-Count(distinct{<[dossierSubmitDate.autoCalendar.Year]={$(=(year(today())-1))}>}key_dossier))
/Count(distinct{<[dossierSubmitDate.autoCalendar.Year]={$(=(year(today())))}>}key_dossier)

The problem I have is that November and December appears also in the line chart but as I haven't yet values for 2023 it causes me an unwanted peak.

Could you please give me an advise where and how in my calculation I can give an exception that only comlpeted months should be considered?

Many thanks in advance

Best regards

Carsten

[TcnCunha_M]

Hi

This case you have to use DATE not Year, because year in a combination from Jan - Dec if make a set analysis like
Date {">=01/01/2023<=YOUR_LATEST_DATE"} 

Last year Date {">=01/01/2022<=YOUR_LATEST_DATE"(But pointing to last year)}  i.e Last year Date {">=01/01/2022<=30/09/2022"} 
then it will work as expected 

[SIDOC1]

Thanks for your answer, but what I would like to avoid is to work with fixed dates as it should work flexible. For example when we are in 2024 it should automatically compare 2024 with 2023. 

Understanding your proposal right, I have to change the formula again.

Isn't there any option to make this run autonomously?

[TcnCunha_M]

Yes, you can use variables to that

you can make a variable for the last year, like
vFirstDayofyear = YearStart(Today())

vFirstDayLastYear = AddMonths(YearStart(Today()),-12)

vActualDate = Max(YOUR_DATE_FIELD)

vActualDateLastYear = AddMonths(Max(YOUR_DATE_FIELD),-12)

You expression will be 
(
//Actual year
Count({< YOUR_DATE_FIELD = {">=$(vFirstDayofyear )<=$(vActualDate )"}>} key_dossier )

//Last Year 

Count({< key_dossier )
)
/
Count(distinct  {< YOUR_DATE_FIELD = {">=$(vFirstDayLastYear )<=$(vActualDateLastYear)"}>} >}key_dossier)

[SIDOC1]

Hi,

Sorry for coming back so late on this subject.

I have tried to put your formula proposals into my diagram, but honestly the result is the same as I would simply use count(key_dossiers).

Many thanks in advance for checking again my incident

[TcnCunha_M]

Hi

yes because you should make accord to you need the main diea it show the varible will do the project

(
//Actual year
Count({< YOUR_DATE_FIELD = {">=$(vFirstDayofyear )<=$(vActualDate )"}>} key_dossier )

)

//Last Year

/
Count(distinct {< YOUR_DATE_FIELD = {">=$(vFirstDayLastYear )<=$(vActualDateLastYear)"}>} >}key_dossier)

[SIDOC1]

Sorry but this solution doesn't work the way I expect it.

Anyway we could maybe re-start the thing a bit as I have already res-structured my formula to:

rangesum(Above(count(distinct {<[messageSubmitDate.autoCalendar.Year]={$(=Year(Today()))}>} if([messageType]='Question' and [messageStatus]='CLSD' and wildmatch([messageFrom_partnerType],'*Requestor'), key_message)),0,12))/
rangesum(Above(count(distinct {<[messageSubmitDate.autoCalendar.Year]={$(=Year(Today())-1)}>} if([messageType]='Question' and [messageStatus]='CLSD' and wildmatch([messageFrom_partnerType],'*Requestor'), key_message)),0,12))-1

I have in addition two formulas concentrating on Year -1 and Year -4.

Generally I am quite happy with the formula and how it is working, but again I have the problem that I currently do not want to see the month of December or in other I do not want to see months which are not completetd

[RamanaKumarChintalapati]

Hi @SIDOC1 ,

Small Suggestion, I think you can restrict the data at source end , Like create a flag and set 
if(date<today(),1,0), then filter only values with 1 , Then you will get the only the months which are completed.

[SIDOC1]

I haven't tried your idea yet, but anyhow I assume that with this still December 22, 21 and 19 would be shown in the diagram and would lead to a not wanted result.

Although December 2023 is then null it will be compared to the other years still having an amount.