topic Cumulative calculation at script level in QlikView

Cumulative calculation at script level

Wed, 08 Sep 2010 06:26:34 GMT

Hi all,

can you please help me in calculation of cumulative values at script lavel for a pivot table.

The data and output file are provided in excel sheet attached.

thanks in advance.

Regards,

GVR

Cumulative calculation at script level

Wed, 08 Sep 2010 06:52:57 GMT

Please find the another output format in the attached spread sheet .

Cumulative calculation at script level

Wed, 08 Sep 2010 07:32:34 GMT

The cumulative values should not change on selection. It is a fixed value as we are going to add amount by each month

Cumulative calculation at script level

Miguel_Angel_Baeyens — Wed, 08 Sep 2010 08:39:51 GMT

Hello Veman,

Using your sample data, might look like

Actual:LOAD Location, Date, Amount, If(Location = Previous(Location), RangeSum(Amount, Peek('Cumulative')), Amount) AS CumulativeRESIDENT TempORDER BY Location, Date;

Hope that helps!

Cumulative calculation at script level

Wed, 08 Sep 2010 10:01:31 GMT

Hi,

Thanks for your response. Its working good but I need to add calender from jan 2010 to dec 2010. It is not displaying all the months.

when I join a calender to this the values getting duplicated.

Thnaks&Regards,

Veman

Cumulative calculation at script level

Miguel_Angel_Baeyens — Wed, 08 Sep 2010 10:54:51 GMT

Hi Veman,

If you link your table to a master calendar (check here), you don't need to replicate values. You will use your field containing Month as dimension in your chart (it will exist as month) it will display all possible values, showing 0 for those months that don't have amount.

Regards.

Cumulative calculation at script level

Wed, 08 Sep 2010 12:17:05 GMT

If you want the cumulative value for every month, you will need to create a table that has every month per location, then left join it with the data to make sure you carry the cumulative values forward.

e.g.


temp:
LOAD * INLINE [
 type,month,val
 a, 1/3/10, 1
 a, 1/5/10, 2
 a, 1/7/10, 3
 b, 1/2/10, 4
 b, 1/4/10, 5
 b, 1/7/10, 6
 c, 1/2/10, 7
 c, 1/6/10, 8
 c, 1/7/10, 9
];


calendar:
load AddMonths('01/01/2010',RecNo()-1,0) as month
autogenerate (24);
temp1:
load distinct type resident temp;
inner join load month,0 as cume resident calendar ;

temp2:
noconcatenate
load RowNo() as sortseq,* resident temp1 order by type,month;
left join load type,month,val resident temp
;
cume:
noconcatenate
load sortseq,type,month,val,if(peek(type)=type,RangeSum(peek(cume),val),val) as cume
 resident temp2
 order by sortseq
 ;

drop tables temp, temp1, temp2;

Re: Cumulative calculation at script level

georgedevries — Tue, 18 Dec 2012 11:58:01 GMT

Hi all,

I've tried the solution, but it does not always give the expecting results when using multiple dimensions.

When selecting all dimensions the results are correct, but when not selecting dimension members, the cumulative values are incorrect.

Note: the cumulatation on total levels goes only if all underlying dimensions exist...

See attached screenshots for example.

Regards,

George

Re: Cumulative calculation at script level

Wed, 27 May 2015 08:06:13 GMT

This might help you..... see the output and attached app

Re: Cumulative calculation at script level

Tue, 22 Mar 2016 11:54:46 GMT

Hello, would you please send me the code of that file ? Here o by email alexbelfas@gmail.com

Re: Cumulative calculation at script level

georgedevries — Tue, 22 Mar 2016 18:08:34 GMT

Hi,

That wouldn't be a problem if you had asked me this 3 years ago...

Regards, George

Verzonden vanaf mijn Samsung-apparaat