Using SQL functions as source of data?

Hi community,

I am using view of postgresql tables as my dataset. And I am using row level security to show unit specific stats. So, I am getting data of user’s unit only and now I want to compare the unit’s data with national average for one visual only. So can I use SQL function as source? If not, is there any other way I can compare unit data with whole data keeping RLS active?

image

These bars are urgency count of diagnosis. Blue line represents total count. And I want to add one more line in this visual for national average count for each diagnosis.

Thanks and regards,
Raghu

@Raghu, you can create another dataset (one without RLS) but at higher levels of grain that will support the avg national day calculation and yet its grain is high such that it doesn’t expose the data specificity or identification. Use this new dataset to create that one visual (fyi - users will be able to export data from this visual so test to make sure it meets your security needs prior to publishing). Additionally you can refer this democentral example that may give you some ideas to explore.

1 Like

Hi @DeepakS ,
Thanks for responding. The solution you provided can solve problem to some extent but in my case, I can not let users select their unit like the region in your example. I am authenticating user in my application and can not allow them to see other unit’s data.

@Raghu, since you are using this total dataset for 1 visual only and unit is not included in your combochart visual, you can filter this specific visual to show only their unit data where as for avg calculation you can use level aware calculation window function avgOver with pre_filter to calculate the total avg value. With pre_filter option, total avg will not be impacted by the unit filter applied to the visual. Refer this blog for more detailed explanation of LAC window function - Create advanced insights using Level Aware Aggregations in Amazon QuickSight.