Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following:
=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(CommonMonth,'YYYY/MM')),-1),'YYYY/MM'))"}>} NumAccts),'#0%')
The first half calculates correctly, it finds the maximum month in my dataset and sums the retained accounts. The second part I want to do the same thing but look 1 year back (Current month but the previous year). I have tried to insert the MAX function into the second part of the statement (red text) with no luck. My aim is to have a static value no matter what date you select as it will always show the MAX date from my dataset.
If CommonMonth is not a dimension in the chart, you can try this
=Num(Sum({$<CommonMonth={$(=max(CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max(CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')
For making this static, you can try this
=Num(Sum({$<CommonMonth = {$(=Max({<CommonMonth>}CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({<CommonMonth>}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')
or this
=Num(Sum({$<CommonMonth = {$(=Max({1}CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({1}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')
Hi - I still need to convert the data from YYYY/MM format into normal date,subtract a year, then convert it back to YYYY/MM which is in my original formula, how would I apply the MAX in that case?
Not sure I follow... would you be able to share a sample?
=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(CommonMonth,'YYYY/MM')),-1),'YYYY/MM'))"}>} NumAccts),'#0%')
The bit in red, this works, but I just need to add the MAX bit in, just not sure where. I need to keep the DATE# part as my COMMONMONTH is in this format YYYY/MM, so when DATE tries to subtract a year, it doesn't understand the format, so I need to keep that bit in, but in your answer the DATE# part was removed, so if I keep the formula the same, but just add MAX, where would I put it? As I tried your formulas and they do not work for me for this reason. Thanks
Don't think I understand... may be someone else can help you better
For the second bit of my formula (red bit) I need to look at the maximum month for example would now be 2018/02 and then select the date one year previous so 2017/02. No matter what date selection a user picks on the CommonMonth selection, it should always show the calculation as the Maximum month in the dataset one year back.
Try this
=Num(Sum({$<CommonMonth = {$(=Max(CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({1}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')
=NUM(SUM({$<CommonMonth={$(=max(CommonMonth))}, CommonMonth= >} RetainedAccounts)/Sum({<CommonMonth = {"$(=date(AddYears(Date(DATE#(MAX({1}CommonMonth,'YYYY/MM'))),-1),'YYYY/MM'))"}>} NumAccts),'#0%')
I get '-' when I do your last suggestion... above is what I did. I need DATE# to convert to string