Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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