Calculating Total Distinct Servers per Month for a PIVOT table

Question & Answer
, , ,
1

Hi
I have a Pivot table grouped by date (month) and name. I need to calculate the value of total distinct servers per month, which should be the same for each name within same month.
I have tried these formulas:

  1. sumOver(sum(distinct_count({server_id},[{event_date}])),[{event_date}])
  2. sumOver(sum(distinctCountOver({server_id},[{event_date}],PRE_AGG)),)
  3. sumOver(distinct_count({server_id}),[{event_date}])
    but nothing works.
    Note: Each server can relate to different names, thus it can be counted several times, one time per month name (Total Distinct Servers column).
    In Total Distinct Servers per Month column same server should be counted just once, so in my example the number 7 should remain as is even when collapsing month rows to one row.
    image
    I can achieve the calculation (distinct_servers_by_month column) using this query:
    select t.*,
    p.distinct_servers_by_month
    FROM table t join
    (select date_trunc(‘month’,event_date) event_month,
    count(distinct server_id) distinct_servers_by_month
    from table
    group by 1) p on date_trunc(‘month’,t.event_date) = event_month
2

Hello @ariebe, I just want to make sure I am on the right track here in order to guide you to the correct solution. The main issue you are facing is that when you collapse the Pivot table, it is adding up the Total Distinct Servers per Month column, am I correct? In that instance, I would try changing the aggregation in your field well to an average, that way when you collapse, it should give the average from each row which will be 7.

If I am not answering the exact question you asked, let me know, but that should resolve the problem with totals!

3

@DylanM thank you for your reply. It was part of the issue yes. I eventually managed to overcome it by adding more group by fields to the partition part of my calculated field. Plus customer’s requirement had slightly changed, which helped me achieve my goal.
Btw, using avg did not work, as it gave me decimal numbers (I expected integers).

1 Like