topic Re: Yearl Comparisson by using Moving Average in App Development

Yearl Comparisson by using Moving Average

SIDOC1 — Thu, 23 Nov 2023 07:01:20 GMT

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

Re: Yearl Comparisson by using Moving Average

TcnCunha_M — Thu, 23 Nov 2023 08:11:19 GMT

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

Re: Yearl Comparisson by using Moving Average

SIDOC1 — Thu, 23 Nov 2023 09:20:00 GMT

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?

Re: Yearl Comparisson by using Moving Average

TcnCunha_M — Thu, 23 Nov 2023 12:23:10 GMT

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)

Re: Yearl Comparisson by using Moving Average

SIDOC1 — Mon, 11 Dec 2023 06:23:14 GMT

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

Re: Yearl Comparisson by using Moving Average

TcnCunha_M — Mon, 11 Dec 2023 14:36:15 GMT

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)

Re: Yearl Comparisson by using Moving Average

SIDOC1 — Thu, 14 Dec 2023 06:10:07 GMT

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

Re: Yearl Comparisson by using Moving Average

RamanaKumarChintalapati — Thu, 14 Dec 2023 06:36:54 GMT

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.

Re: Yearl Comparisson by using Moving Average

SIDOC1 — Fri, 15 Dec 2023 13:04:31 GMT

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.