I’m having trouble with bringing in two measures that have different time grains. What do I mean by that? I have an example dataset that has the number of tickets sold by day, but the goal is at a monthly level. In IBM Cognos, we would mark the date as a “time dimension”, and then tell it what to do with the monthly data that is on each of daily rows, take the first value, the last value, or the average of all the values.
In this screenshot, I am showing what it is currently doing, and what I want it to do.
Here’s my SQL that generates a simple dataset for this analysis.
select ‘cricket’ as sport, date ‘20220101’ as sale_date, 23732 as tickets_sold, 100000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220102’ as sale_date, 18972 as tickets_sold, 100000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220103’ as sale_date, 27484 as tickets_sold, 100000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220104’ as sale_date, 28562 as tickets_sold, 100000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220102’ as sale_date, 17304 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220104’ as sale_date, 19703 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220106’ as sale_date, 21892 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220108’ as sale_date, 23468 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220110’ as sale_date, 27753 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220112’ as sale_date, 31822 as tickets_sold, 170000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220114’ as sale_date, 36463 as tickets_sold, 170000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220203’ as sale_date, 23732 as tickets_sold, 80000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220205’ as sale_date, 18972 as tickets_sold, 80000 as monthly_goal
union all
select ‘cricket’ as sport, date ‘20220207’ as sale_date, 27484 as tickets_sold, 80000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220202’ as sale_date, 17412 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220204’ as sale_date, 19730 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220206’ as sale_date, 21819 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220208’ as sale_date, 23519 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220210’ as sale_date, 27834 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220212’ as sale_date, 31092 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220214’ as sale_date, 36363 as tickets_sold, 200000 as monthly_goal
union all
select ‘soccer’ as sport, date ‘20220216’ as sale_date, 38568 as tickets_sold, 200000 as monthly_goal
