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: 
subhohere
Contributor
Contributor

Distinct Dimension with calculated column

Hi Qlik Gurus, 

I got stuck in a problem which I am trying to fix and not able to achieve. I want to filter out the data based on a particular dimension (i.e Dec in the table). I want to take the highest 'Dec' value and shown it in the chart. I want to achieve this UI level in calculated dimension. Basically want to remove the line item with low Dec value and take the max(dec) value

I have achieved some part where the Dec value is different for same ID the result is correct taking the max of Dec and getting one line item,. 

Ex: For ID 11234 I am getting the first line item where Dec is 9. 

But problem started where both 'Dec' values are same. Code is not working and throws out both line items. for ID 93451 & 98735. 

I have to achieve this in UI level as per client requirement and cannot make changes in script level. Can anyone please help me out. Below is my expression I have used in calculated column. 

if(Dec=Aggr(max(Dec),[ID]),Dec)

So, for each ID we will have only one line item with the highest Dec value or if Dec value is same we will have only one distinct line item.

IDFirst nameLast nameAddressCityDec
11234JohnMartinStreetTX9
11234JohnMartinStreetTX5
93451AliceosmanStreetIL8
93451AliceosmanStreetIL8
67891RichardRapStreetTN4
98735JessicaSheeyStreetOH7
98735JessicaSheeyStreetOH7
78901SaraStarfordStreetIL2
78901SaraStarfordStreetIL4
15678MariaSpanStreetTX2
Labels (4)
7 Replies
Vegar
MVP
MVP

Try this expression: 

=MAX(total <ID> Dec) 

clipboard_image_0.png

 

sunny_talwar

What other dimensions do you have in your chart... do any of those dimensions have different value for 93451 & 98735? Dimensions will always show unique values based on the combination of dimensions you have... if all the dimensions have the same value, they should not repeat....

subhohere
Contributor
Contributor
Author

The data provided by me is dummy data. When I applied this is real client qvw I am still getting  error in calculated column. The table is straight table where I am using your code for the calculated column. 

subhohere
Contributor
Contributor
Author

Sunny, 

Thanks for replying so quickly. I have quite few dimensions in the straight table. I want to achieve a distinct record based on the Max(Dec) value. 

sunny_talwar

That make sense, but did my question make sense? If you have a dimension called Zip Code for example which doesn't have the same value.... Let's say 93451 have two rows with Zip Code 111001 and 111002 and you add Zip Code as a dimension.... then you will see two rows...  because both the rows still have Dec = 8...  What would you want to see for Zip Code if this happens? Randomly show 1? Show nothing? Show concatenated value?

subhohere
Contributor
Contributor
Author

ok I got it. All other column values for duplicates are same except the Dec value.  It was showing error. But I got the code now, got it working. 

I have created a calculated column and it's working fine except one. But the problem is for one ID it's not taking the if condition and getting suppressed due to ticking 'Suppress Null value'. It is taking null. But Dec has got 5 & 8. When I am taking the max of 'Dec' in text box for this particular id it's showing up correctly.   Not sure what is the character in there for the Dec value. 

Can you please tell a workaround for this?

sunny_talwar

Would you be able to share a dashboard where we can see this issue?