SumOver in a month per user

Hi,
I’m finding a way to count distinct companies having app_gross_com from 1M-5M in that month. This is my data


I want it to look like this

Thanks a lot !

Hi @thecuon119 ,
Did you checked out.

BR

2 Likes

I did it. Btw, Could you tell me the difference between

  1. sumOver(app_gross_com,[company_name, truncDate(‘MM’, transactions_date)], PRE_AGG)
  2. sumOver(app_gross_com,[company_name,truncDate(‘MM’, transactions_date)])
  3. sum(app_gross_com, [company_name,truncDate(‘MM’, transactions_date)])
    for me ? I already read materials but i didn’t really get it. Thanks a lot !!

Hello @thecuon119, were you able to get the desired output from the distinct_countIf function that @ErikG suggested? That seems like the best course of action, or you could wrap the distinct_count function within an ifelse statement.

As for the 3 calculations you mentioned, the first 2 functions are Level Aware Calculation - Window Functions (LAC-W) and the third one is a Level Aware Calculation - Aggregate Function (LAC-A).
The main benefit to LAC-W calculations is that you can add the PRE_AGG and PRE_FILTER identifiers. PRE_AGG will run the aggregation before computing display-level aggregations, and PRE_FILTER runs before applying filters. These things are good to test out and run a few different ways so you can see how it impacts the data that is being returned. Here is some more documentation highlighting the differences between LAC-W and LAC-A.

I’ll mark this as a solution for now, but if you still need further assistance achieving the distinct_count value you need, let me know!