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: 
chloe
Contributor III
Contributor III

Total in KPI depend on dimension in table

Hi guys,

Please check my print screen below.
The first table is a table with the data without any manipulation.
In the second table I have a column with IF statement  :
IF(Main Account = Account , Campaign , Null() )
And based on this "if", the SUN(Revenue) changes from 300$ to 100$ (I'm filtering nulls out of the table therefore I'm getting only the 100$ row).

I need to show in KPI object the SUM(Revenue) as shown in the second table = 100$.

How can I do that? 

chloe_0-1645119630273.png

Thank,

Labels (1)
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

Feels like this should do it,

Sum(IF([Main Account] = Account , Revenue))

Not sure if it should need an AGGR, can you try?

Cheers,

Chris.

View solution in original post

5 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Feels like this should do it,

Sum(IF([Main Account] = Account , Revenue))

Not sure if it should need an AGGR, can you try?

Cheers,

Chris.

chloe
Contributor III
Contributor III
Author

Hi Chris,

 

Thank you for your response. 

It's not working.
I tried :

Sum(IF([Main Account] = Account , Revenue))

and also:

Sum(aggr(Sum(IF([Main Account] = Account , Revenue),Campaign))

 

Any other idea? 🙂

Digvijay_Singh

not sure but the sample data you shared, the expression by  @chrismarlow looks okay

Pl share better sample if its not working.

Digvijay_Singh_0-1645129767943.png

 

  

GabrielFonsecaz
Contributor II
Contributor II

Your 'IF' expression set 'Null()' if the anwser is not the same in the column "Main Account" and "Acount".  It´s a syntax error. 


If( Truth you are searching for, If its Right Result, If it´s wrong result) 

I could not understand what you want with that ''IF'' expression. Maybe if you need to show the revenue in the front, you could use the a simple SUM in the KPI. 

Example: 

SUM({<"Main Account"="Account">}Revenue)

And the result '100' is right actually. 








chloe
Contributor III
Contributor III
Author

Thank you all for your help.

I solved it using @chrismarlow idea but instead of AGGR by Campaign , I used Account : 

sum(aggr(sum(if([Main Account] = Account ,Revenue))),Account))

 

Thanks!! 🙂