Top X in Geo Analytics Layers

[Lech_Miszkiewicz]

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.

• As a dimension i have a store name - i want users to be able to select user friendly point (name) from the map and i want my current selection to reflect store name
• for each store i have a latitude and longitude available (so i can use them as a location attribute)
  • !!! 2 stores can have exactly the same latitude and longitude (as they can be located in the building) so the coordinates can't be used as dimension
• each store has a measure against it, lets just say it is sum(SalesAmount)

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:

• What would be the best way to achieve top 50 stores on bubble layer in this case?
  • keep in mind that I cannot just put latitude and longitude to dimension (as in one location like for example shopping centre i can have more than one store and my results would be than wrong) and users want to have store names shown in selection when they click on bubble
  • data set can be huge, so running calculated dimensions or complicated measures is something i can look at at the very end as a temporary workaround
• Do i really have to create complex calculated dimension (with Rank, Aggr etc to achieve this) or Set Analysis on a measure?

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

[Anonymous]

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.

1. create the dimension as a simple concatenation of the lat/long like so:  ='['&Longitude&','&Latitude&']'
2. In the location options tab of the point layer set the "Location Source" to "Dimension"
3. Create your measure as you would for any other chart and set your top 50 limit in the dimension.
4. Create a custom Info Bubble so that you have appropriate information in the pop up bubble on hover/touch. Perhaps the easiest way would be to just embed a an existing visusalisation.

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

[Lech_Miszkiewicz]

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

[Patric_Nordstrom]

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

[Lech_Miszkiewicz]

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:

• dimension: [store name]
• location: here i have single field build in script using GeoMakePoint function: [store geo point] ([lat,long])
• measure: sum({<shop_type={'shoes'}>} sales) - because this layer should only show stores selling shoes
• no selections can be applied on shop_type field - as we still want to look at all store types on other charts and tables.
  

result is that the 2 bubbles are created:

• 1 for store selling shoes which is OK
• and 1  for store where shop type is equal to 'clothes' -which is NOT OK - this bubble is created because such store exist and has valid location coordinates, the measure though sum({<shop_type={shoes}>} sales) gives result = 0 and normaly i would suppress 0 values by unticking checkbox "Include zero values".

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

[Patric_Nordstrom · Accepted solution]

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

[Lech_Miszkiewicz]

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