Hi, I wanna calculate the number of publisher having pub commission between 500M and 1B in that month. However, my below calculated field doesn’t work. Thanks in advance!
ifelse(
sum({sum_app_pub_commissions}, [{publishers_login_name}]) >= 500000000
and sum({sum_app_pub_commissions}, [{publishers_login_name}]) <= 1000000000,
distinct_count({publishers_login_name}),
null)
I want my table look like this
Hi @thecuon119,
Can you try this?
distinct_countIf(
{publishers_login_name},
sumOver({sum_app_pub_commissions}, [{publishers_login_name}], PRE_AGG) >= 500000000
AND sumOver({sum_app_pub_commissions}, [{publishers_login_name}], PRE_AGG) <= 1000000000
)
I already checked and it seems incorrect. There are only 3 people meeting that requirement in Jan while the number in table is 31
There was an issue with the sumOver.
Create a calculated field for the month (replace {date field} by your actual date field).
Month = truncdate(‘MM’, {date field})
Then add it to the partition for the sumOver:
distinct_countIf(
{publishers_login_name},
sumOver({sum_app_pub_commissions}, [{publishers_login_name}, Month], PRE_AGG) >= 500000000
AND sumOver({sum_app_pub_commissions}, [{publishers_login_name}, Month], PRE_AGG) <= 1000000000
)
distinct_countIf(
{publishers_login_name},
sumOver({sum_app_pub_commissions}, [{publishers_login_name}, truncDate(“MM”, {transactions_sales_time})], PRE_AGG) >= 500000000
AND sumOver({sum_app_pub_commissions}, [{publishers_login_name},truncDate(“MM”, {transactions_sales_time})], PRE_AGG) <= 1000000000
i)
I added month to the field but it’s still wrong, It should have been 0 0 3 in Jan but i got 1 1 3 instead. As long as that person meets one requirement in one category, iit shows in other categories too but i want it exactly according to the categories
One way you can troubleshoot this is by creating a flat table that contains your unaggregated data. Then add an action to your pivot table. If you click on the “1” in your pivot table, your table of unaggregated data should show you who is contributing to that count.