replace the hardcoding formula

[bharatkishore]

Hi All,

@sunny_talwar

I have written the below formula :

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"} 

>} Consumption), 0, Month(Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC)))))

But every time i don't want to change that 96(those many months(7 years) i have). Because next month i have to change to 97 then next month 98...

I don't want it to be hard coding. 

Can you please help me how it to be automated.

Please let me know if you need anything more.

Thanks,

Bharat

[sunny_talwar]

So it seems that the lower limit is fixed at a certain date? Will it always be fixed there? meaning with each passing month you will keep on increasing the number? 96, 97, 98, .... 110, ...., 120, ....150... etc? or does it every reset?

[bharatkishore]

Thank you sunny bhai for reply.
yes sunny bhai. because i am trying to calculate YTD and this is a monthly refresh.
Since i have 7 years i.e. 96 months i am giving 96 then again next month i will do a refresh then the number will be 97 and the next month it will be 98..

[sunny_talwar]

But next year will the number is 96 + 12 = 108 and then then year after it will be 96 + 24 = 120?

[bharatkishore]

No Sunny Bhai, it will be 96+1 =97 for jan and for feb it will 97+1=98... because it is a monthly refresh.
please let me know if something is not clear..

[sunny_talwar]

Right but what will happen a year from now

Jan2019 - 97

Feb2019 - 98

Mar2019 - 99

Apr2019 - 100

May2019 - 101

Jun2019 - 102

July2019 - 103

Aug2019 - 104

Sep2019 - 105

Oct2019 - 106

Nov2019 - 107

Dec2019 - 108 or 96?

Jan2020 - 109 or 97?

Does my question may sense?

[bharatkishore]

Jan 2020 will be 109 and feb 2020 will be 110 and so on...
Since i need to do YTD the formula what i have written is :
Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -96))
96 is the number of months i have. Since i have data from jan 2012 to dec 2018 it is 96 months.
So i have given hardcoding which i don't want to do everymonth.
Please let me know if anything is not clear Sunny bhai..

[sunny_talwar · Accepted solution]

If that is the case... then why don't you just use this

MonthYear = {">=$(=Date(MakeDate(2012)))<=$(=Date(Max(MonthYear)))"}>}

Because the beginning will always be Jan 2012... then why do you even need to do -96, -97,... and so on?

[bharatkishore]

Thank you Sunny Bhai.. it worked perfectly...One final question
for Date(MakeDate(2012))) is it is possible to give some min(MonthYear).. can we do anything like that..

[sunny_talwar]

I think so... may be just Min({1} MonthYear)