topic Re: Pivot Partial sum calculation in QlikView

Pivot Partial sum calculation

Mon, 20 Feb 2017 12:45:48 GMT

Hi Folks

I have attached Source file, pls find the attached xl sheet

My Req is

i have 4 columns Month,Id1,Id2,Value

In my pivot table Dimensions will will be

Month,

Id1,

Id2

(Id2 will be dragged as cross table )

expression will be like

if(id2=7,expression,sum(value))

for all the other id's will be calculated as sum(value) but for id2=7 calcultaion will be like

id=1

sum(value) for

id=1 aggr(sum(value) ,month,id1)/sum(value) (Horizontal value)

i;e is id2 total value of previous month is 140 (previous month total)

id1 horizontal total value is 80

Final result should be for id1=1 is 130/80 = 1.625 (previous month total value/current month horizontal for total for 1)

like wise id1=2 ,3,4,5,6......

please find the attached appllication .

please feel free to ask in case of any misunderstanding the requirement .

ganeshsvm — Mon, 20 Feb 2017 13:23:01 GMT

Missing attachment of XL sheet(Data), and also you can give an XL that shows your required result.

-Ganesh

Mon, 20 Feb 2017 14:02:59 GMT

Apologies

PFA

Tue, 21 Feb 2017 07:49:25 GMT

Hi Guys ,

Could you please help me on this

Anil_Babu_Samineni — Tue, 21 Feb 2017 09:34:14 GMT

Manoj, Can you check this? I've not tested

If(id2 = 7,sum(TOTAL <id2> val) / sum({$<id2= >} total <Date, id1> val),Sum(val))

If(id2 = 7,sum( aggr( Sum(val), Dim1, Dim2, Dim3 ...)) / sum({$<id2= >} total <Date, id1> val),Sum(val))

// Here, Dim1, Dim2, Dim3 are your Object Dimensions

Sat, 11 Mar 2017 13:24:43 GMT

Hi Anil,

i tried with above expr.. but no luck