topic Re: Spill Logic in QlikView

Spill Logic

brijesh1991 — Wed, 06 May 2015 07:01:03 GMT

Hi,

As shown in attached image,

I have Achievement and Plan at some ID level in two different table. I want to spill Remaining Plan in next month.

For Example:

Apr Acutal Plan: 100

Apr Ach: 50

Spill = 100-50= 50

Apr Derived Plan: 100 (since it's first month of year)

May Actual Plan: 200

May Ach: 100

Spill= 200-100 =100

May Derived Plan= May Actual Plan + Previous month spill =200+50 =250

Jun Actual Plan: 250

Jun Ach: 50

Spill= 250-50=200

Jun Derived Plan = Jun Actual Plan + Previous month spill = 250 + 100 = 350

And so on...

Kindly help me to derive this logic.

Re: Spill Logic

Anonymous — Wed, 06 May 2015 07:25:14 GMT

Hi,

You can use Above() to get previous month's Spill. The derived column can be calculated as

sum(Actual)+if(isnull(above(sum(Actual)-sum(Ach))),0,above(sum(Actual)-sum(Ach)))

Regards,

Greeshma

Re: Spill Logic

brijesh1991 — Thu, 07 May 2015 04:24:39 GMT

I want to calculate from backend and want to store the final derived field into QVD.

Re: Spill Logic

Anonymous — Thu, 07 May 2015 04:37:57 GMT

Hi,

You can use the previous function in the script

load * inline [
Month, Actual, Ach
Apr, 100, 50
May, 200, 100
Jun, 300, 150
Jul, 500, 100
Aug, 100, 50
Sep, 150, 50
Oct, 250, 100
Nov, 300, 250
Dec, 400, 150
Jan, 500, 250
Feb, 600, 300
Mar, 500, 200
];

load *,
Actual + if(isnull(Previous(Spill)),0,Previous(Spill)) as Derived;
load
Month,
Actual,
Ach,
Actual -Ach as Spill
Resident t1;
drop table t1;

Regards,

Greeshma