topic How to draw a chart with sophisticated expression? in QlikView

How to draw a chart with sophisticated expression?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to draw a chart with sophisticated expression?

Tue, 02 Aug 2016 09:27:44 GMT

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

Re: How to draw a chart with sophisticated expression?

Anonymous — Tue, 02 Aug 2016 11:30:14 GMT

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

Re: How to draw a chart with sophisticated expression?

Anil_Babu_Samineni — Tue, 02 Aug 2016 11:44:17 GMT

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

Re: How to draw a chart with sophisticated expression?

Tue, 02 Aug 2016 15:37:57 GMT

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

Re: How to draw a chart with sophisticated expression?

sunny_talwar — Wed, 03 Aug 2016 00:03:30 GMT

Is this what you want?

Dimension:

Month

Expression

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

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

Re: How to draw a chart with sophisticated expression?

Wed, 03 Aug 2016 02:09:23 GMT

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?

Re: How to draw a chart with sophisticated expression?

sunny_talwar — Wed, 03 Aug 2016 08:33:30 GMT

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