Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
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
I might be wrong. but may be you just need this
Aggr(RangeSum(Above(Sum({<MonthYear>} Consumption), 0, Month(Only({<MonthYear>} Month)))), (MonthYear, (NUMERIC)))
Sure, what do you want to replace it with?
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..
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..
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?