Method to use custom measures from 2 separate charts in 1 common chart
Hi,
Trying to calculate a metric using 2 data sources - the numerator has to come from data source 1 and the denominator has to come from data source 2. Am able to calculate the numerator and denominator in 2 separate charts (please refer attached snippet). Would be great to understand how to create a 3rd chart where these values can be divided by one another. Thanks!
-
Hi Abhishek Bansal
with two data sources, do you mean that your data comes from two different tables?
In this case, you should join the two tables by creating a view in the database or use a custom data source (or wait for the 7.0 release). Any of these options isn't so easy because the granularity of the aggregations looks to be different, so you are likely going to use windows functions.
Could you explain how the data sources' data relation to each other?Regards,
Candido -
Hi Candido,
Thanks!
Yes, meant two different tables:
Table 1 - it gives count of locations serviced by an internet provider with a particular technology in a given U.S. Census Block
Table 2 - it gives total count of locations in every U.S. Census BlockSo, essentially need to find serviceable locations from table 1 and total locations from table 2, and then divide to get % coverage. Also, have appended Census Block polygon shapes to both tables for the calculations to update as we zoom in or out on the map.
Joining the two is not very feasible. Can you please further explain how this can be handled via custom data source and window functions?
-
HI,
The two data sources must be combined to calculate the values in a single calculation, so both tables must be joined.
Given the calculation's different cardinality, the windows function must also be used.
So I ended up using a custom data source (or creating a view in the database) with a query like this.
SELECT
technology,
provider,
p.census_id,
number_of_locations,
number_of_total_locations,
first,
sum(number_of_total_locations*first) over () as global_total_locations
FROM
(select *, CASE
WHEN (ROW_NUMBER() OVER (PARTITION BY census_id)) = 1 THEN 1
ELSE 0 END AS first
from census_provider_location) as p
INNER JOIN census_total_localtions AS t ON t.census_id = p.census_id
Everything is working, but for some limitations on Heavy Immerse/Query Engine when you filter for some census sections, the filter isn't pushed down the resulting sub-queryWithout filtering, everything is ok.
Filtering for the census_id 2, the percentage is wrong because the count of global locations (sample global etc.) is 19 instead to be 11 because the filter isn't pushed down.
To be sure I'm using the same kind of data as you, I created two tablescensus_provider_location
╭─────────┬────────┬────────────┬───────────────────╮
│census_id│provider│ technology │number_of_locations│
├─────────┼────────┼────────────┼───────────────────┤
│ 1│Verizon │Licensed FWA│ 2│
│ 1│Verizon │Licensed FWA│ 1│
│ 1│Verizon │Licensed FWA│ 5│
│ 1│ViaSat │GSO │ 1│
│ 1│ViaSat │GSO │ 1│
│ 1│ViaSat │GSO │ 3│
│ 2│ViaSat │GSO │ 1│
│ 2│ViaSat │GSO │ 1│
│ 2│ViaSat │GSO │ 1│
│ 2│ViaSat │GSO │ 1│
╰─────────┴────────┴────────────┴───────────────────╯and
census_total_localtions;
╭─────────┬─────────────────────────╮
│census_id│number_of_total_locations│
├─────────┼─────────────────────────┤
│ 2│ 8│
│ 1│ 11│
╰─────────┴─────────────────────────╯Do these mimic the ones you are using?
Anyway, I'll ask internally if there is a better approach to do this calculation.
Best Regards,Candido
-
Hi Abhishek Bansal,
I finally ended up with something working with using the view/custom data source measure. In the end it was a problem with how I wrote the custom measure and not an Immerse/HeavyDB limit ;)
So, I created a custom data source with this query that join the two tables and return the total number of the total of location for census just 1 time for each census_id.SELECT
technology,
provider,
p.census_id,
number_of_locations,
CASE
WHEN (ROW_NUMBER() OVER (PARTITION BY p.census_id)) = 1 THEN number_of_total_locations
ELSE 0
END AS number_of_total_locations
FROM
census_provider_location as p
INNER JOIN census_total_localtions AS t ON t.census_id = p.census_idThen to calculate the percentage, I created this custom measure.
Now all the results in the dashboard are correctly filtered (in this case I'm filtering for the census_id 1).
The bad thing about this approach is that everything falls back to a CPU execution.
Anyway, let me know if this approach is ok for you.
Regards,
Candido -
Hi Candido,
Thanks for looking into this!
Seems like this could work. In order to try this out, would be great to know your thoughts on the following:
- if I append census block polygon shapes to the data and plot it on map as a choropleth, will the calculations automatically update as per area selected in the map view?
- how can I enable custom data source (unable to find an option for this in "Select Data Source" feature on the chart - please refer below snippet)
Thanks again for all your help!
Best,
Abhishek
-
Hi,
For the choropleth is better to use a different table and join with the custom source / or view.
I'm saying that because you could hit some issues, and you would consume some memory.Use the Choropleth Chart and using a table with the census_id and the geometry to draw; this way you should be able to filter also the data in the custor data/view when you pan and zoom in tyhe CC.
If you need a more specific example, please let me know.
Candido -
Hi,
Thanks for the response!
Can you please let me know how to enable custom data source option (unable to find it)?
Also, just to confirm my understanding of the above - I create a new separate table with census_id and geometry to plot the choropleth, and then use the Geo-join option inside choropleth chart to link that new separate table with the custom data source?
Best,
Abhishek
-
Hi,
to enable a custom data source, you have to turn the data source manager to true in the server.json file
"ui/enable_custom_source_manager": true
the docs about the server.json file can be found here
When you set this parameter to true you should see this when creating a new chart
anyway you can also create a view in the database as you source without the need of enabling the custom data source
Please sign in to leave a comment.
Comments
8 comments