Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to ask a question regarding Top X values calculated in Geo Analytics.
Lets look at the scenario:
I would like to see on the map my top 50 best performing stores in Australia.
Task is:
SHOW 50 STORES WITH BIGGEST sum(SalesAmount) VALUE
Ideally i would like to use LIMITATIONS on DIMENSION (like on second screenshot marked in yellow) to show my Top 50 best performing stores. The issue is that the LIMITATIONS refer to first measure which is actually LOCATION, so my Top 50 gets calculated based on latitude and longitude values which is wrong.
This makes the LIMITATIONS functionality useless.
Concept of mixing "location" with measure seems to be a very annoying feature at this stage.
So the questions i have are:
patricn Patric - would you be able to comment if there is a better way of doing this (TOP X)?
btw - little bit of the topic, but i think it is a voice of all Qlik GeoAnalytics users:
The another very annoying thing is lack of the basic functionality on GeoAnalytics layers which is "include zero values" checkbox. We have to create conditional statements on location field to hide locations for which measure is = 0. Below functionality exists on built in Qlik objects as GeoAnalytics is part of Qlik now i would expect this to be added ASAP.
Thanks
Lech
Hi Lech,
Top 50, it's trickier than I thought to change the Limitation behavior, first measure is the standard in both QV and Sense. Removing location info as a measure is not nice either, breaks backward compatibility and the flexibility for specifying location.
Exclude null, same here, data handling->include zero values, std behavior is that all measures to be 0. This can be seen in the table or scatter plot.
Calculated dimensions can give side effects that's true, in my example all fields were in the same table, fields on the fly, which makes the selection bar to show only 'State' and not the whole expression.
Thanks,
Patric
Hi Lech,
You make some good points, I'm sure Patric will have an interesting response.
I had an idea that might work in addressing your main problem. It does involve using the lat/long in the dimension but a custom Info Bubble could be used to show the details of the multiple stores.
This approach should work with large volumes as you are utilising the standard limit approach and not performing any complex logic. I've only done a quick test of this but it might be worth a go. Otherwise perhaps Patric will have a better answer.
Cheers,
Rod
Hi Rod,
thanks for your comment and reply. I will not be able to use your solution as it will produce wrong results due to single location linked to multiple stores. (the same coordinates for multiple stores in shopping centres)
I am using your approach everywhere i can but it cannot be used in this case
cheers
Lech
Hi Lech,
Include null, there is a check box on the dimension. So there only have to be one filtering expression in the dimension.
=if(len(city)>0,city) or =if(lo>-180 and la>-180, dim1) for instance.
I normally set flags at load time, sanity checking coordinates, empty fields, doing lookup of places etc in order to do filtering easier in runtime.
Top 50, QGA extension has little use of the limitation (since which measure is not changeable) unless if you do as you say. The alternative today is to limit the dimension with an if expression (using the include null checkbox) or set analysis =aggr(Only({<State = {"=Rank(Sum(Trails)) <=10"}>}State), State) in the dimension.
The new map chart will have sorting order on layers. So you will be able to limit the layer to 50 items and set order to Sum(sales) descending.
Thanks,
Patric
Hi Patric,
Thank you for your comment.
---------------------------------------------------------------------------------------------------------------------------------------
Top 50 - I am using different approach but very similar to your suggestion. I am using rank function anf If statemnt on location field: If(aggr(rank(sum(sales),4,4),store) <=50,location,null()) and this seems to work OK
I understand that there are no plans to re-think and separate location field from measure filed so LIMITATIONS could be normaly used
Your suggestions will still work as workarounds though.
---------------------------------------------------------------------------------------------------------------------------------------
Exclude 0 values - I think you have misunderstood my query. I was not talking about Null values on dimension, but locations which possibly can return 0 value for measure. This feature is on built in charts in Add-ons section...
Scenario:
I have 2 shops A and B
Shop A (shoes) has exactly the same coordinates as shop B (clothes) beacuse they are both in the same building but one is selling only shoes and another clothes. In our system both stores are treated as separate entities and they are analyzed separately due to product categories they are selling.
Now i have bubble layer with following setup:
result is that the 2 bubbles are created:
This can't be done in QGA layers by simply unticking the checkbox.
Instead I have to use conditional statement on location field like: If(sum({<shop_type={shoes}>} sales) =0,Null(),[store geo point]). This is the only currently working solution
I do not want to use conditional statement on dimension because it will screw my current selections when interacting with map. Selections will pass also if statement results to current selections...
I think it makes development very hard.
Now i am involved in project which is heavily based on QGA. More less i will have to create around 1000 layers in roughly 180 Qlik sense apps. All of them are impacted by above mentioned problems and in all of them i have to use workarounds as mentioned above, hence my hopes were that maybe you guys are planning to include those features in some near future in GeoAnalytics layers, hence this post.
To summerize.
I think sorting will help partialy fix the complecity for Top X issue,
Suppress 0 values - It would be great to have this implemented.
I appreciate your help!
Thanks & regards Lech
Hi Lech,
Top 50, it's trickier than I thought to change the Limitation behavior, first measure is the standard in both QV and Sense. Removing location info as a measure is not nice either, breaks backward compatibility and the flexibility for specifying location.
Exclude null, same here, data handling->include zero values, std behavior is that all measures to be 0. This can be seen in the table or scatter plot.
Calculated dimensions can give side effects that's true, in my example all fields were in the same table, fields on the fly, which makes the selection bar to show only 'State' and not the whole expression.
Thanks,
Patric
Hi Patric,
Thanks for reply.
I understand limitations we now have due to location being a first measure. I realized that Limitations as well as 0 values are actually both based on measure and since one of the measure is location the current solution has an significant implications on results we see on the map.
I am not sure what was the concept of using a measure as an one of the location information. I am sure there were pros and cons for this solution.
The issue i have is that i have to use If statement or rank function as per your suggestion and i know it will work, but ultimately it is a performance killer!!!
I seriously think this should be reconsidered by Qlik R&D team as long term current solution does not follow standards implemented in other charts and of top of that requires either additional data modeling or performance drop when using maps.
Just to make you aware i measured 2 scenarios:
Data set out of 2.7 million of rows in fact table i wanted to present on map roughly 4000-8000 records linked to individual data point (linked to a heatmap layer based - heatmap grouping +/- 16000 lat-long locations)
1. scenario - if statement /rank used either in dimension / or location response time +/- 8 seconds
2. scenario - additional columns created only to support available values for subset of data (but also confusing users when selecting from the map - as the field reflected in current selection is different) +/- 1 second
2nd scenario is the one i had to go with in order to achieve required performance. It is confusing users a lot and i get questions from business why we are not consistent with field names across our apps.
If conditional statements or "confusing changes" in data model are the only solutions we currently have i will close this topic with a hope that the approach for geoanalytics layers location field/placeholder will be revisited by Qlik R&D and possibly moved to its dedicated placeholder which could be backwards compatible?
thanks and regards
Lech