Dynamic YTD expression

[rtr13]

Hi All,

I have seen numerous questions in regards to YTD calculations, but none in regards to a dynamic YTD calculation.

I am currently using the following set analysis to pull the sum of prior YTD Charges:

=SUM({$<ContactDTS = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( (Today()),-12))'}>}Charges)

But, when selecting a different year, my value drops to zero. In addition, for some reason, my number isn't accurately reflecting the sum of Charges within the field. 

Has anyone ever created a Prior YTD expression that allows is dynamic to the year selected?

Thanks,

Rose

[AlecPtn]

Hello,

I believe your values are dropping to zero because you are using a separate date selection rather than 'ContactDTS' as your set analysis is set to respect today() and not your current date selection.  This makes the months being looked at static to today().

What I believe would work for you is create a field, if you don't already have one, called ContactYears which is simply a collection of years which are related to their respective dates.  For example:

ContactYears     ContactDTS

2019                       Feb2019

2019                       Jan2019

2018                       Dec2018

2018                       Nov2018

2018                       Oct2018

That way you could either simply select a year for your YTD, or you could change your set analysis to look something like this.

=SUM({$<ContactYear = P(ContactYear), ContactDTS=, OtherDateSelections=>} Charges)

Please let me know if this helps.

*Side note: I make a lot of assumptions about your fields and data, and if I am wrong anywhere please feel free to let me know.

[rtr13 · Accepted solution]

Hi,

Thank you for your feedback, it definitely helped! Instead of making a new column, I noticed I forgot to reference my filters within my expression. I ended up using the following expression to find a Dynamic YTD:

=Sum({<YEAR=,Month_Short=,ContactDTS={'>=$(=YearStart(Max(AddYears([ContactDTS],-1))))<=$(=MonthEnd(AddYears(Max([ContactDTS]),-1)))'}>} [Charges])

Since I didn't reference my ContactDTS or filters to begin, after going back to 2018, my expression was unable to pull any dates since it was only looking one year back from my Contact date!

Thanks for the help, I appreciate it!

Best,

Rose