Filtering The percent of Total by Data Range

Ask the Community
1

I want to calculate the percent of total and was able to follow the guide here.
this is how my code look like:

with pt_all as (
  select
    sum({{ #reporting_views_sor_data.active_after_180_days }}) +
    sum({{ #reporting_views_sor_data.hired }}) as total
  from {{ #reporting_views_sor_data }}
),
pt_by_category as (
  select
    {{ #reporting_views_sor_data.days_range }},
    sum({{ #reporting_views_sor_data.hired }}) as hired
  from {{ #reporting_views_sor_data }}
  group by 1
)

select
  pt_by_category.days_range,
  pt_by_category.hired,
  pt_all.total
from pt_all join pt_by_category on true
order by days_range asc

However, I would like to add data range filter to this calculation. There is additional column in the source data date_range and I need to recreate this model every time I dynamically change the date_range in the dashboard.

Another approach I tried is using this model:

SELECT
  application_id,
  date_range,
  days_range,
  active_after_180_days,
  hired,
  sum(active_after_180_days) over() as total_active_after_180_days, --this gives me the total
  sum(hired) over() as total_hired --this gives me the total
from
  {{ #reporting_views_sor_data }}

and then I created business calculation sum(hired) / (max(total_active_after_180_days)+max(total_hired))

but the total_hired and total_active_after_180_days won’t refresh when I filter which ruins the percentages.

2

Hi @jurajkapasny. Thanks for sharing this. Would you mind going over a description of what you want to achieve, assuming you have not written any modelling codes yet.

So if you could be kind to share a detailed description of your specific use case, and some snippet of what your raw data on that table looks like.

There could be a possible workaround for you, I just want to be sure I clearly understand your scenario.