Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

petersvendsen
New Contributor II

KPI Sum expression problem

Hi

I try to make a KPI that calculate total cost for rearing a car

The data are spreed in several spread sheets.

In sheet 1 I have

Repair no                    Part changed

1                                   Tires

2                                   Engine

3                                   Screw

4                                   Tires

5                                   Screw

In sheet 2 I have

Part changed          Price

Tires                         100

Engine                      8100

Screw                       1

Front glass               300

I tried with this expression

Sum([Price]*Count([Repair no]))

But it doesnt work, can you give me a hint whats wrong?

Thanks,

Peter

19 Replies
MVP
MVP

Re: KPI Sum expression problem

May be this

Sum([Price])*Count([Repair no])

krishna_2644
Valued Contributor III

Re: KPI Sum expression problem

check this out.you forgot to aggregate with respect to each and every part changed for both sum and count.

1.PNG

shilpan9166
Contributor II

Re: KPI Sum expression problem

See attached qvw

sahadevpatil140
New Contributor III

Re: KPI Sum expression problem

May be this Expected output-

KPI Issue.JPG

petersvendsen
New Contributor II

Re: KPI Sum expression problem

Thanks all of you, it is solved with your input

krishna_2644
Valued Contributor III

Re: KPI Sum expression problem

Pls close the thread

petersvendsen
New Contributor II

Re: KPI Sum expression problem

Hi,

When I use Sum([Price])*Count([Repair no]) I get a wired number

Krishna what expression did you use?

krishna_2644
Valued Contributor III

Re: KPI Sum expression problem

= sum({$< [Part changed]={'Engine'}> }Price)* count({$< [Part changed]={'Engine'}> }[Part changed]) +

  sum({$< [Part changed]={'Tires'}> }Price) * count({$< [Part changed]={'Tires'}> }[Part changed]) +

  sum({$< [Part changed]={'Screw'}> }Price)* count({$< [Part changed]={'Screw'}> }[Part changed])

As you have different parts and each have respective counts and aggregation, the below expression yields wierd numbers.

Sum([Price])*Count([Repair no])

petersvendsen
New Contributor II

Re: KPI Sum expression problem

Thanks

What if the numbers of parts on the list is huge fx several hundreds, is there a better way to solve the issue?