Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Partner
Partner

Thank you Rob Appreciate your input.

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