Percentof Total with distinct count

Question & Answer
,
1

Hello, I want to count the percent of total with aggregating {mức thu nhập tích luỹ}, but it turns into errors

I’d like to turn this calculated field into percent for adding a percent line on this visual:

distinct_countIf({Transactions Publisher}, truncDate(‘DD’, {publishers_ctime}) <= truncDate(‘DD’, ${Dateto}) AND truncDate(‘DD’, {transactions_sales_time}) <= truncDate(‘DD’, ${Dateto}) AND truncDate(‘DD’, {Latest_transactions_sales_time}) <= addDateTime(-${DaysWihtOutTransaction}, ‘DD’, ${Datefrom}))

image
image999×643 25.7 KB

=> My percentoftotal calculated field below seems incorrect:

percentOfTotal(avg(distinctCountOver(ifelse(truncDate(‘DD’, {publishers_ctime}) <= truncDate(‘DD’, ${Dateto}) AND truncDate(‘DD’, {transactions_sales_time}) <= truncDate(‘DD’, ${Dateto}) AND truncDate(‘DD’, {Latest_transactions_sales_time}) <= addDateTime(-${DaysWihtOutTransaction}, ‘DD’, ${Datefrom}), {Transactions Publisher}, null), [{mức thu nhập tích luỹ}], PRE_AGG)))

image
image998×591 14.8 KB

Thanks in advance !

2

Hi @thecuon119
Is it possible to share a sample data for this?

Regards
Vetri

3

Hello @thecuon119, I think to get the solution you are looking for you will want to break this up into 2 fields.

Rather than using distinct_countIf, create a calculated field ifelse statement that will return the Transactions Publisher field or NULL. I’ll call it Transactions Calc to reference.

Then you can make a second calculated field where you use the result of the ifelse statement to manually make the percentage:
distinct_count({Transactions Calc})/sum({mức thu nhập tích luỹ})

Or if the distinct count and sum need to be partitioned you can do this:
distinctCountOver({Transactions Calc}, [{Partition Field}])/sumOver({mức thu nhập tích luỹ}, [{Partition Field}])

When you are trying to use different types of aggregations to get your expected result, it is often better to change the method to avoid aggregation errors. I hope this helps lead you to a working solution!

1 Like