Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator
Creator

How to Display KPI value matching to Sum Total instead of Expression Total

Hi Team,

I am calculating custom expression in the table chart containing different dimensions as below and on selecting expression total in the chart the numbers were wrong and it is giving correct result on Sum total. 

My requirement is, I need to show this metric value in KPI and it will display Expression total by default. How to achieve the Sum Total value in KPI. Also, is there any other way to write below expression by removing If conditions so that it will show correct value in KPI

=if(Sum([Recoverable])=0, Sum({<Set analysis>}Amount),
if(Sum([Minimum Recoverable])>0,
(Sum({<Set analyis}>}Amount)-Sum({<Set Analysis}>}[Recoverable]))))

Regards,

Polisetti

Labels (4)
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

I think you need to use AGGR, so setting aside your set analysis & I don't know what your dimensions are, so have just put one in called 'Dim', something like;

sum(if(aggr(Sum([Recoverable]),Dim)=0, aggr(Sum(Amount),Dim),
if(aggr(Sum([Minimum Recoverable]),Dim)>0,
(aggr(Sum(Amount),Dim)-aggr(Sum([Recoverable]),Dim)))))

Giving;

20200805_2.png

You would put your set analysis back in & a comma delimited list of the fields to aggregate over for AGGR (check the docs for more details).

Do share a few more details if this does not help.

Cheers,

Chris.

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I think you need to use AGGR, so setting aside your set analysis & I don't know what your dimensions are, so have just put one in called 'Dim', something like;

sum(if(aggr(Sum([Recoverable]),Dim)=0, aggr(Sum(Amount),Dim),
if(aggr(Sum([Minimum Recoverable]),Dim)>0,
(aggr(Sum(Amount),Dim)-aggr(Sum([Recoverable]),Dim)))))

Giving;

20200805_2.png

You would put your set analysis back in & a comma delimited list of the fields to aggregate over for AGGR (check the docs for more details).

Do share a few more details if this does not help.

Cheers,

Chris.

polisetti
Creator
Creator
Author

Hi Chris,

Thank you for your reply. They are multiple dimensions in my case and I just added the lowest granularity dim in the KPI by using the Aggr function. 

I am getting results as expected but need to verify thoroughly as the expression contains lot of condition set analysis. 

Regards,

Jaswanth