Hi! I am trying to find the average spend for traffic per content distributor, customer, and month. The complexity is that I have data per day, and I need to display the traffic state per day with average values per month in one table. The current month can display data only up to yesterday. However, an average planned budget per day should be divided by the total number of days in the current month.
Here are the fields to count days in a month (these work correctly):
count_days_in_month_prefilter = countOver({event_date}, [cd, {customer}, truncDate('MM', {event_date})], PRE_FILTER)
count_days_in_month_level2 = sum(max({count_days_in_month_prefilter}, [campaign, {internal_id}])) ( this filed I need to display correct sub total and total.)
This field displays the correct budget per day, in subtotal and total:
budget_distinct = sumOver(sum({budget_sum_month}), [cd, customer]) / max({count_days_to_budget_division})
The field “Budget per day” does not work appropriately for total sum:
budget_per_day = budget_distinct / count_days_in_month_level2
I expect budget_distinct = (250) / count_days_in_month_level2 (62) = 4.03, but I got 0.13 instead. The values per day is correct, but the sub total and total sum are not.
It looks very strange. What am I doing wrong? Thanks for any help