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: 
VivenReddy
Partner - Creator
Partner - Creator

How to compare 2 dimension columns in a chart in another dimension column

Hi All

I seem to be struggling with something that might be quite simple. I am unable to get the correct result when comparing 2 dimensions columns and placing the result in another dimension column.

image.png

I am trying to compare [Supplier] with [main supplier]. When they are equal the value in [compare] should be 1. As you can see the correct value is being returned for some rows. 

See qvw attached.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think you just need to add NODISTINCT in your Aggr().

=aggr(if(aggr(NODISTINCT FirstSortedValue(Supplier, -aggr(sum(Total_kg),Fruit,Supplier)),Fruit)=Supplier,1),Fruit, Category, Price_kg)

Secondly, it would be easier if you defined main supplier as an expression rather than a dim. If both "main supplier" and "compare" were expressions, you could define compare like this:

=Supplier = [main supplier]

if main supplier was an expression, you could probably eliminate most of the aggr.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

4 Replies
ajayrai
Partner - Contributor III
Partner - Contributor III

Hi Viven,

The app you have attached is not having the same data set you have used to show case the snapshot which shows the problem. I tried creating dummy data set and tried this comparison and it works fine. 

I suggest you try Only(supplier)=Only(main_supplier). 

Let me know if this works for you. 

If not working you can share your application with the chart and data set which is having issue.

 

 

Cheers,

Ajay

VivenReddy
Partner - Creator
Partner - Creator
Author

It is the correct dataset Ajay. Main Supplier is a calculated dimension:

aggr(FirstSortedValue(Supplier, -aggr(sum(Total_kg),Fruit,Supplier)),Fruit)

So how would I compare a regular dimension to a calculated dimension and return a value of 1 in the Compare column when these values are equal.

Look at the Compare Suppliers chart in my qvw.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I think you just need to add NODISTINCT in your Aggr().

=aggr(if(aggr(NODISTINCT FirstSortedValue(Supplier, -aggr(sum(Total_kg),Fruit,Supplier)),Fruit)=Supplier,1),Fruit, Category, Price_kg)

Secondly, it would be easier if you defined main supplier as an expression rather than a dim. If both "main supplier" and "compare" were expressions, you could define compare like this:

=Supplier = [main supplier]

if main supplier was an expression, you could probably eliminate most of the aggr.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

VivenReddy
Partner - Creator
Partner - Creator
Author

Thank you Rob Appreciate your input.

This logic will be going into a pivot table and that's why they have to be dimensions.