Max month

[Anonymous]

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.

[sunny_talwar]

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%')

[sunny_talwar]

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%')

[Anonymous]

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?

[sunny_talwar]

Not sure I follow... would you be able to share a sample?

[Anonymous]

=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

[sunny_talwar]

Don't think I understand... may be someone else can help you better

[Anonymous]

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.

[sunny_talwar]

Try this

=Num(Sum({$<CommonMonth = {$(=Max(CommonMonth))}>} RetainedAccounts)/Sum({<CommonMonth = {"$(=Date(AddYears(Max({1}CommonMonth), -1), 'YYYY/MM'))"}>} NumAccts),'#0%')

[Anonymous]

=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