If-expression divided on If-expression not working

[helge_jorg]

Qlik experts,

I have three seets in Qlik were I want to get values and calculations from based on a if-statement. I want to get the value if one cell is empty. I get the right values when I use the expression when I calculate the “utslipp” based on company share, and the same for the “produksjon”. When I divided the two expressions, I get the wrong answer. It does not provide the value based on the company share, but it return the same value for all.

Expressions:

Utslipp
(If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare]))) *0.01)
*
(sum({<[Utslipp.KomponentKode]={'CO2'}, [Utslipp.Utslipp]={'Luft'}>} [Utslipp.Verdi]))

Produksjon
(If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare]))*0.01)
*
(sum([Produksjon.Value])))

Utslipp/Produksjon

(If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare])))*0.01)

*
sum({<[Utslipp.KomponentKode]={'CO2'}, [Utslipp.Utslipp]={'Luft'}>} [Utslipp.Verdi])

/

(If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare])))*0.01)
*
(sum([Produksjon.Value]))

Data

[Vegar · Accepted solution]

It's har to say without having any data to look at, but I notice that your paranthesis usage is a bit different when comparing the Utslipp expression to the Utslipp/Produksjon numerator.

There is also a difference between the Produksjon expression and the Utslipp/Produksjon denominator.

Have you tried this expression:

((If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare]))) *0.01) *(sum({<[Utslipp.KomponentKode]={'CO2'}, [Utslipp.Utslipp]={'Luft'}>} [Utslipp.Verdi])))
/
((If(Len(Trim([Licensee.Date licensee to]))=0, (Sum([Licensee.CompanyShare]))*0.01) * (sum([Produksjon.Value]))))

It is also possible to reference to a column in a QlikView table, it could be a workaround for you.

Try to use this expression: [Utslipp]/[Produksjon]  or reference by column number: Column(3)/Column(2)

[helge_jorg]

Thanks, Vegard for your suggestion. 

Your expression did not help - the result is same for shareholders. 

I have attached a test-app where you can have a look at the data. 

Thanks for your help!

[Vegar]

It looks correct to me.

If you manually calculate row by row CO2 / Prod you will get 46,08 on every row.  Maybe the issue lies in your undelying data and/or your two initial expressions?