Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

alwinsch
Contributor

Pivot Table - layout and expressions

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

1 Solution

Accepted Solutions
alwinsch
Contributor

Pivot Table - layout and expressions

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

1 Reply
alwinsch
Contributor

Pivot Table - layout and expressions

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