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

[bharatkishore]

It is version problem Sunny Bhai...If  i put it as numeric in QV12 it works fine..We will be getting in ascending order.. This also you had only taught me. Please let me know anything if you need. Please see the below image:

[sunny_talwar · Accepted solution]

Try this

Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}, Consumption = {"*"}>} Aggr(RangeSum(Above(Sum({<MonthYear>} IB), 0, Month(Only({<MonthYear>} Month)))), cat, (MonthYear, (NUMERIC))))/
Sum(TOTAL <[Month-Year]>{<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}, Consumption = {"*"}>} Aggr(RangeSum(Above(Sum({<MonthYear>} IB), 0, Month(Only({<MonthYear>} Month)))), cat, (MonthYear, (NUMERIC))))

[bharatkishore]

Thank you Sunny Bhai... it worket perfectly.. But can you please tell me what have you done.. Difficult to understand.. Sorry to take your time Sunn Bhai...

[sunny_talwar]

I am not sure what to explain... you wanted to divide the expression by the total for the month... I just did that.. is there anything specific you don't understand?

[bharatkishore]

I will check again and take some time to analyze the expression. I will work on it Sunny Bhai... Thanks a lot again and again...If i get any queries i will let you know .. Thank you so much...........Really thanks Sunny Bhai..