Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
helge_jorg
Creator
Creator

If-expression divided on If-expression not working

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]))

 

reults.png

Data

Data.png

 

 

 

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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)

View solution in original post

3 Replies
Vegar
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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?

 

image.png