Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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
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.
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
Thank you Rob Appreciate your input.
This logic will be going into a pivot table and that's why they have to be dimensions.