Calculated field in table

Question & Answer
1

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)

image
image1098×617 43.7 KB

I want my table look like this

2

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
)
3

image
image1760×774 83.9 KB

I already checked and it seems incorrect. There are only 3 people meeting that requirement in Jan while the number in table is 31

4

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
)
5

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)

image
image716×461 14.8 KB

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

6

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.