topic Re: Pivot table Calculation Help in QlikView

Pivot table Calculation Help

amit_saini — Tue, 25 Aug 2015 06:48:40 GMT

Hi Folks ,

I have a pivot table :

Dimmension: STDDATE , Location

Expression : Sum(Values)

Requirement : I need to divide "CHESTERFIELD" values with other location row wise.

e.g for STDDATE- 2015-08-17 . the entire row need to be divide by 47 , similarly for 2015-08-18 row should be divided by 46 and so on...

Kindly suggest how this can be achieved .

Thanks,

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 06:51:12 GMT

I read Before and After functions which works only for Pivot table few days back, will give a try now.

Re: Pivot table Calculation Help

Kushal_Chawda — Tue, 25 Aug 2015 06:53:35 GMT

Can you provide the sample app?

Re: Pivot table Calculation Help

amit_saini — Tue, 25 Aug 2015 06:59:21 GMT

Please see the attachment

Thanks,
AS

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 07:24:44 GMT

I have PE so cannot see your file, please see attached if it can help.

Something like below can work -

If(ColumnNo()=1,Sum(Value)/After(Sum(Value),2),

If(ColumnNo()=2,Sum(Value)/After(Sum(Value),1),

If(ColumnNo()=3,Sum(Value)/Sum(Value),

If(ColumnNo()=4,Sum(Value)/Before(Sum(Value),1)))))

Re: Pivot table Calculation Help

amit_saini — Tue, 25 Aug 2015 07:47:36 GMT

Digvijay,

This is working till column 4 , cam you help to build this script till coulmn 8.

Thanks,
AS

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 08:02:31 GMT

Just a sample,

If(ColumnNo()=1,Sum(Value)/After(Sum(Value),2),

If(ColumnNo()=2,Sum(Value)/After(Sum(Value),1),

If(ColumnNo()=3,Sum(Value)/Sum(Value),

If(ColumnNo()=4,Sum(Value)/Before(Sum(Value),1),

If(ColumnNo()=5,Sum(Value)/Before(Sum(Value),2),

If(ColumnNo()=6,Sum(Value)/Before(Sum(Value),3),

If(ColumnNo()=7,Sum(Value)/Before(Sum(Value),4),

If(ColumnNo()=8,Sum(Value)/Before(Sum(Value),5),

))))))))

I had to change sorting of Location to load order. Also attaching the data I have used.

Re: Pivot table Calculation Help

Tue, 25 Aug 2015 08:04:19 GMT

Hi. In expression you mast write

if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum(Value) )

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 08:11:01 GMT

Great! I feel this is better and dynamic solution. I just checked it is working perfectly on the data I used.Thanks!

Re: Pivot table Calculation Help

Tue, 25 Aug 2015 08:11:26 GMT

Hi Amit,

Try the following:

Sum(Values)/ Sum(Total <STDDATE> {<Location={"CHESTERFIELD"}>} Values)

Re: Pivot table Calculation Help

amit_saini — Tue, 25 Aug 2015 08:42:19 GMT

Good one , just one issue :

Re: Pivot table Calculation Help

amit_saini — Tue, 25 Aug 2015 08:47:39 GMT

Looks like this will work:

if(Location<>'CHESTERFIELD',sum({<Location-={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value) ,sum({<Location={CHESTERFIELD}>} Value)/sum(TOTAL<STDDATE> {<Location={CHESTERFIELD}>} Value))

Thanks,

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 08:52:25 GMT

Oh yeah, this can be corrected by sum(Value)/sum(Value) in else condition of expression.

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 08:54:18 GMT

I mean in the expression shared by SergeyKostin

or you can use the one by

or mine one...:)

Re: Pivot table Calculation Help

Digvijay_Singh — Tue, 25 Aug 2015 08:55:24 GMT

in the 2nd line pl read - by esuyanti