Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I don't know how to make a chart like "FA*/MAPE". I would like to draw it from table "FA/FD (T)". The difficulty here is the formula for FA & FD are calculated for 3 months, not only a month like FA* & MAPE.
Thanks,
Dong
Is this what you want?
Dimension:
Month
Expression
1)
=If(2-(sum({<Type={"Lag-3 FCT"}>} Qty)/sum({<Type={"Act. Sales"}>} Qty)) > 0,
RangeSum(Above(Sum({<Type={"Act. Sales"}, Month>} Qty), 0, 3))/
RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)))
2)
Sum(Aggr(
fabs(RangeSum(
RangeSum(Above(Sum({<Type={"Act. Sales"}, Month>} Qty), 0, 3)),
-RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)))), [Merging code], Month))
/
Sum(Aggr(RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)), [Merging code], Month))
Hi All,
For easier, I would say that how to calculate for 3 months (current, previous, and before previous month).
The formula for FA & FD are as following:
FA = Sum(last 3 months of Act. Sales)/Sum(last 3 months of Lag-3 FCT)
FD = Absolute(Sum(last 3 months of Act. Sales)-Sum(last 3 months of Lag-3 FCT))/Sum(last 3 months of Lag-3 FCT)
Example: for Jan'16
Jan'16 FA = (Act. Sales Nov'15 + Act. Sales Dec'15 + Act. Sales Jan'16)/(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16)
Jan'16 FD = Absolute((Act. Sales Nov'15 + Act. Sales Dec'15 + Act. Sales Jan'16)-(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16))/(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16)
Thanks,
Dong
Try this
=Sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>} Act. Sales) / Sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>}Lag-3 FCT)
In Variable Definition
vMaxDate = Max(DateField)
vMinDate= Floor(AddMonth(Max(DateField),-3))
I taken Manoj Expression,
=Sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>} [Act. Sales]) / Sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>} [Lag-3 FCT])
But, DONG i don't know what are you expecting
Hi Anil,
You can find attachement to see the details in the original post.
Below is what i am looking for
"For easier, I would say that how to calculate for 3 months (current, previous, and before previous month).
The formula for FA & FD are as following:
FA = Sum(last 3 months of Act. Sales)/Sum(last 3 months of Lag-3 FCT)
FD = Absolute(Sum(last 3 months of Act. Sales)-Sum(last 3 months of Lag-3 FCT))/Sum(last 3 months of Lag-3 FCT)
Example: for Jan'16
Jan'16 FA = (Act. Sales Nov'15 + Act. Sales Dec'15 + Act. Sales Jan'16)/(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16)
Jan'16 FD = Absolute((Act. Sales Nov'15 + Act. Sales Dec'15 + Act. Sales Jan'16)-(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16))/(Lag-3 FCT Nov'15 + Lag-3 FCT Dec'15 + Lag-3 FCT Jan'16)"
Is this what you want?
Dimension:
Month
Expression
1)
=If(2-(sum({<Type={"Lag-3 FCT"}>} Qty)/sum({<Type={"Act. Sales"}>} Qty)) > 0,
RangeSum(Above(Sum({<Type={"Act. Sales"}, Month>} Qty), 0, 3))/
RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)))
2)
Sum(Aggr(
fabs(RangeSum(
RangeSum(Above(Sum({<Type={"Act. Sales"}, Month>} Qty), 0, 3)),
-RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)))), [Merging code], Month))
/
Sum(Aggr(RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)), [Merging code], Month))
This is exactly what I am looking for. Many thanks!
A small adjustment for showing only selected months by using Dimension Limits
FA=If(2-(sum({<Type={"Lag-3 FCT"}>} Qty)/sum({<Type={"Act. Sales"}>} Qty)) > 0,
Sum(Aggr(RangeSum(Above(Sum({<Type={"Act. Sales"}, Month>} Qty), 0, 3)),[Merging code],Month))/
Sum(Aggr(RangeSum(Above(Sum({<Type={"Lag-3 FCT"}, Month>} Qty), 0, 3)),[Merging code],Month)),0)
Do you agree or any way else to do that?
If it is giving you what you need. I am all good with you making the change