How to draw a chart with sophisticated expression?

[#1]

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

[#2]

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

[Anonymous]

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))

[Anil_Babu_Samineni]

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

[#5]

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)"

[sunny_talwar · Accepted solution]

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))

[#7]

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?

[sunny_talwar]

If it is giving you what you need. I am all good with you making the change