Display the highest difference and its corresponding values - Qlik Sense

[manisha10]

Hi All, 

I have a table with Actual and Target Values. I want to display a table with max difference between Actual and Target along with the corresponding values in Col A and Col B.

A	B	Actual	Target
Commercial	Unsecured	20	11
Consumer	Secured	11	11
Consumer	Unsecured	5	11
Commercial	Secured	4	11

Desired Output:

A	B	Actual	Target
Commercial	Unsecured	20	11

Thank you! 

Qlik Sense Business Qlik Sense Desktop  

[Frank_Hartmann]

You want the calculation in script or in frontend?

[manisha10]

In frontend. 

[Frank_Hartmann · Accepted solution]

Dimensions: A, B, Actual, Target

Expression: if(fabs(Actual-Target)=max(Total(fabs(Actual-Target))),fabs(Actual-Target),null())

and supress nullvalues for expression

[manisha10]

Thanks a lot Frank! 😊 It is working on the above dataset. 

Now I'm searching how to suppress the rows with null values!  

MaxValue = if(fabs(Actual-Target)=max(Total(fabs(Actual-Target))),fabs(Actual-Target),null())

I have written these expressions to hide the rows with null values 

Dimension A: aggr(if(len([MaxValue])),A),A)

Dimension B: aggr(if(len([MaxValue])),B),B)

Dimension Actual: aggr(if(len([MaxValue])),Actual),Actual)

Dimension Target: aggr(if(len([MaxValue])),Target),Target)

But it is giving error.

Thank you!

[Frank_Hartmann · Accepted solution]

DimA =if(aggr(fabs(Actual-Target),A,B,Actual,Target)=max(Total aggr(fabs(Actual-Target),A,B,Actual,Target)),A,null())

DimB =if(aggr(fabs(Actual-Target),A,B,Actual,Target)=max(Total aggr(fabs(Actual-Target),A,B,Actual,Target)),B,null())

DimTarget =if(aggr(fabs(Actual-Target),A,B,Actual,Target)=max(Total aggr(fabs(Actual-Target),A,B,Actual,Target)),Target,null())

DimActual =if(aggr(fabs(Actual-Target),A,B,Actual,Target)=max(Total aggr(fabs(Actual-Target),A,B,Actual,Target)),Actual,null())

Expression =fabs(Actual-Target)

[manisha10]

Thanks a lot!! It is working 😊😊