Comparing filtered and non-filtered values on same visual

Question & Answer
, , , ,
1

I have two tables where one has a filter applied to it and another one doesn’t. The filters are for different locations, so the one on the left has a specific set of locations, while the one on the right is for every location.

The main goal is to be able to create a line graph with the filtered and unfiltered values on the same graph with different lines. I’m not sure how to do this.

Another nice bonus would be to get all the values on the same table instead of using two different tables with filters for one of them.

image
image1507×532 27 KB

2

Hi @jp207
You can try using a new calculated field with Level Aware Aggregate/ Calculate functions. Please see example in the link below: https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Calculation-Level-Aware-Calculations

In the above link you can open a copy of underlying analysis by clicking edit icon on the left as shown in the below diagram
open-analysis-in-democentral

3

I’m still a bit confused how to get a new calculated field with LAA functions. Currently the oe (and ob) are calculated fields. Here’s how I get the oe values:

sum(ifelse({ov_experience} >= 8 AND {ov_experience} <= 10, 1, 0)*100) / count({ov_experience})

4

Hello @jp207, if that calculated field above is working for your filtered value line, you can try these steps to set your unfiltered line.

Create a field to run your ov_experience ifelse statement without the sum:
ovExperienceCheck = ifelse({ov_experience} >= 8 AND {ov_experience} <= 10, 1, 0)

Now, we will use that field in a sumOver function and divide it by a distinctCountOver function:
unfilteredValue = sumOver({ovExperienceCheck}, [], PRE_FILTER)*100/distinctCountOver({ov_experience}, [], PRE_FILTER)

You may need to play around with how to add in the *100 portion of the second statement, and if PRE_FILTER doesn’t provide the expected result, you can try replacing with PRE_AGG.

Then both your original field and your unfilteredValue field can be added into your line graph and you should receive the expected result! I will mark this response as the solution, but if the problem remains unresolved let me know and we can figure out another option.