Pivot Table - layout and expressions

alwinsch — Wed, 14 Mar 2012 09:05:44 GMT

Hi experts,

I'm rather new to Qlikview and need some hint to solve my problem with pivot tables.

I have 2 excel files which I load into QV.
first Excel "80148 01.xlsx"
second Excel "80148 02.xlsx"

After the load statement, my data looks like this.
OfficeID, Month, characteristics, domain, amount
88148, 01, Life, aa, 100
88148, 01, Life, ab, 50
88148, 01, Varia, cc, 200
88148, 02, Life, aa, 150
88148, 02 Life,ab,100
88148, 02, Varia,cc, 650
88148, 02, varia,cd, 50

I want a result:

OfficeID Characteristics 01 02

88148 life 150 250
88148 Varia 200 700

I use as expression for column 01: if(match(text(month),'01'),Sum ({$}[Netto jaar premie]),0)
I use as expression for column 02: if(match(text(month),'02'),Sum ({$}[Netto jaar premie]),0)
But I get in every column the value 0

Only if i ad in my pivot after characteristics the month
then i get

OfficeID Characteristics month 01 02
88148 life 01 150 0
88148 Life 02 0 250

I want to see the first result!

Please help

Kind Regards,

Alwin

------

Hi all,

I found the solution myself 🙂

I changed the expression

from: if(match(text(month),'01'),Sum ({$}[Netto jaar premie]),0)

to: sum(if(match(text(month),'01'),[Netto jaar premie],0))

Kind Regards,

Alwin

Message was edited by: alwinsch

Pivot Table - layout and expressions

alwinsch — Wed, 14 Mar 2012 09:53:00 GMT

Hi all,

I found the solution myself 🙂

I changed the expression

from: if(match(text(month),'01'),Sum ({$}[Netto jaar premie]),0)

to: sum(if(match(text(month),'01'),[Netto jaar premie],0))

Kind Regards,

Alwin

topic Pivot Table - layout and expressions in QlikView

Pivot Table - layout and expressions

Pivot Table - layout and expressions