Here is the translation of your text into English:
“Good evening, I need my data to show the daily average of this indicator, the counts are distinctcountif. In the case of the image, for example, the final result should be 0.84%, not 1.49%. I would also like this value to appear in the charts, and when I change the aggregation of the chart to daily, the value for that specific day should appear. In the table, the result for the day is correct, but the ‘total’ (monthly result) should be the average of the days in that month. I tried using SumOver divided by the distinct days, but it didn’t work. Do you have any idea what I should do?”
The totals are not relevant in the monthly total, so there wouldn’t be a right or wrong; it is only relevant when I look at the daily result, in which case it is correct. For the month, quarter, or year, it will always be the average of the daily result.
Count 1: distinct_countIf({cpf_cnpj}, column1=‘sim’ AND {column2}=‘PF’ AND column3=‘Life’)
Count 2: distinct_countIf({cpf_cnpj}, {column2}=‘PF’)
Hi @Lucas1,
It’s been awhile since last communication took place on this thread, did you have any additional questions or were you able to find a work around for your use case?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
If you just do Count1/Count2, it’s taking Count1 for all the days in your visual and dividing by Count2 for all the days in your visual. If you want to calculate the average of all daily values of Count1/Count2, you need to create your calculated field differently.
Day:
truncDate('DD', date)
Count 1:
distinct_count(ifelse(column1=‘sim’ AND {column2}=‘PF’ AND column3=‘Life’, {cpf_cnpj}, null), [Day])
If your dates are already at the day granularity, you can skip the first calculated field with the truncDate function and just use your Date field directly (instead of Day) in Count 1 and Count 2.
