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 · Accepted solution]

Or just Min(MonthYear)... not entirely sure what you have and trying to do... so just guessing 🙂

[bharatkishore]

Thank you Sunny Bhai.. this also worked. I have replaced like below:

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

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Consumption), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, (MonthYear, (NUMERIC)))))

The values are fine.. please let me know if i am doing anything wrong in the formula..

Thank you again Sunny Bhai..

[sunny_talwar]

I might be wrong. but may be you just need this

Aggr(RangeSum(Above(Sum({<MonthYear>} Consumption), 0, Month(Only({<MonthYear>} Month)))), (MonthYear, (NUMERIC)))

[bharatkishore]

No Sunny Bhai it didn't work.. but when i have replaced with your earlier suggestions everything worked perfectly....

One final help Sunny Bhai... i have the below formula :

sum(IB)/sum(TOTAL<[Month-Year]> {<cat>} IB)

Numerator i have replaced i.e.

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(
RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,
Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))
, cat, (MonthYear, (NUMERIC)))))

Now i need your help to replace the denominator i.e. sum(TOTAL<[Month-Year]> {<cat>} IB)

Can you please help me how to replace the denominator..

[sunny_talwar]

Sure, what do you want to replace it with?

[bharatkishore]

I need to replace this sum(TOTAL<[Month-Year]> {<cat>} IB) in the below one:

If(Sum(Consumption) <> 0,Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Aggr(
RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,
Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))
, cat, (MonthYear, (NUMERIC)))))

[sunny_talwar]

I am not sure what that means. Would you be able to share a sample and the output you expect to see from it?

[bharatkishore]

Soory Sunny Bhai for late reply..

PFA file.. Here the actual formula is sum(IB)/sum(TOTAL<MonthYear> {<cat>} IB)

For numerator(sum(IB)) it should be replaced by which i have done :

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

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

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

Now for 

sum(TOTAL<MonthYear> {<cat>} IB)

i need to put in below formula

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

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

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

Please let me know if you need anything more..

[bharatkishore]

Soory Sunny Bhai for late reply..

PFA file.. Here the actual formula is sum(IB)/sum(TOTAL<MonthYear> {<cat>} IB)

For numerator(sum(IB)) it should be replaced by which i have done :

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

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

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

Now for 

sum(TOTAL<MonthYear> {<cat>} IB)

i need to put in below formula

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

RangeSum(Above(Sum({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} IB), 0,

Month(Only({<MonthYear={">=$(=Date(Min(MonthYear)))<=$(=Date(Max(MonthYear)))"}>} Month))))

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

Please let me know if you need anything more..

[sunny_talwar]

Something is not right... why are we seeing the chart go up and down... is this chart showing correctly?

Shouldn't we be seeing a chart where each bar should be higher than the previous month's bar?