Budget vs actual (different time grains)

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

Hi,

please follow the period to over date functions as QS support lot of function. You can explore some others as per your requirements.

Why is monthly_goal being aggregated as Average?
image

It should be Sum and that should give you the same figures you are expecting.

Darcoli
setting monthly goal to Sum does not do what I want it to, now my summary for the month is 400k, I want it to be 100k for Jan/cricket, 80k for Feb/cricket, 170k for Jan/soccer, 200k for feb/soccer


The issue that I’m having a problem is that I’m mixing my time dimensions on my values. Tickets sold is by day while monthly goal is at a month level.
When I show by day, this is what I get, you can see the repeating monthly goal amount on the daily summary.

Naveed,
I’ve tried a dozen different formulas of PeriodToDateSumOverTime and am failing miserably. I can’t get it to add soccer (100k) and cricket (80k) for Jan 2022 to come up with a jan subtotal of 180k . Needless to say February’s monthly numbers are wrong and February’s YTD are off because I can’t get Jan and Feb monthly numbers working.

Hi,

You can use LAA to calculate at the required granularity and use POST_AGG_FILTER for visual calculations

Calculated Field to test : monthly_goal_post_agg

sumOver(min({monthly_goal}),[sport,{sale_date}],POST_AGG_FILTER)

Below Screenshot :

Regards,
Koushik

That did it. I think I had tried sumOver, but not with Post_agg_filter. I was even able to add a %

Thanks

Also, the avg also works, as in
image

Think of the instance where a new section of the stadium is approved for occupancy by the local jurisdiction mid month. Mgmt will want to sell the seats for the remainder of the month, so the monthly goal was increased for the last 15 days of the month.