topic Re: YTD at a given moment in time selected by user in App Development

YTD at a given moment in time selected by user

George_Metrakos — Sat, 16 Nov 2024 05:35:59 GMT

Hello Community!

I'm trying to set a Year-to-date (YTD) expression such that the user can select the point in time (could be in the past) and the expression would calculate the YTD to that point. I'm focussed on the MonthYear as my main time dimension. Since this is a financial application I have budget (future) data as well as historic.

My current expression is only returning the sum of "AMOUNT" for the MonthYear selected, ie. if the user selects May2019, (link to Year = 2019 in data model) I would expect that this would sum AMOUNT from January to May of 2019 but it only gives me the AMOUNT for the selected May2019 period instead... Any ideas?

Thanks!

//YTD
=Sum({$<
Year={"$(=Max(Year))"},
MonthYear={"<=$(=Max(MonthYear))"}
>}AMOUNT)

Re: YTD at a given moment in time selected by user

sunny_talwar — Thu, 13 Jun 2019 12:32:10 GMT

May be you need MonthYear formatting added to your set analysis for MonthYear

=Sum({$<Year = {"$(=Max(Year))"}, MonthYear = {"<=$(=Date(Max(MonthYear), 'MMMYYYY'))"}>} AMOUNT)

Re: YTD at a given moment in time selected by user

George_Metrakos — Thu, 13 Jun 2019 14:16:28 GMT

Thank you @sunny_talwar , I appreciate your insight- unfortunately, even the date formatting isn't giving the correct result... for some reason, the "<=$" would sum up every MonthYear within the current Max(Year) but it's not... any other suggestions?

Thanks very much!

George

Re: YTD at a given moment in time selected by user

sunny_talwar — Thu, 13 Jun 2019 14:57:01 GMT

Would you be able to share a sample where we can see the issue?

Re: YTD at a given moment in time selected by user

George_Metrakos — Thu, 13 Jun 2019 15:00:01 GMT

Hi @sunny_talwar ,

I was able to figure it out- a syntax issue... the correct expression is:

=Sum({$<
Year={$(=Max(Year))},
MonthYear={"<=$(=Max(MonthYear))"}
>}AMOUNT)

The extra " on the Year set was throwing it...

Thanks again for taking a look!

George

Re: YTD at a given moment in time selected by user

George_Metrakos — Thu, 13 Jun 2019 15:34:17 GMT

Hello again @sunny_talwar ,

If you still have a chance to review, the problem I have now is to build the Prior YTD using the same user selection.

In my example, the user selected "May2019" as the MonthYear. Therefore, my Prior YTD sum should be from Jan 2018 to May 2018 inclusive. I'm using:

In my master calendar, I have a link to MonthNum, so May is "5" so I thought I'd use this:

Year={"=$(=Max(Year)-1)"},
MonthYear=,
MonthNum={"<=$(=Max(MonthNum))"},

I understood that adding the "MonthYear=" would negate the user selection so that the expression can utilize it to identify the Year and Month required...

However, the expression is not working- any thoughts?

Thanks,

George

Re: YTD at a given moment in time selected by user

sunny_talwar — Thu, 13 Jun 2019 17:28:46 GMT

Should be this

Year = {"$(=Max(Year)-1)"},

Re: YTD at a given moment in time selected by user

sunny_talwar — Thu, 13 Jun 2019 17:29:10 GMT

Year = {$(=Max(Year)-1)},

Re: YTD at a given moment in time selected by user

George_Metrakos — Thu, 13 Jun 2019 19:17:33 GMT

Thanks @sunny_talwar !