How to create an average according to the date filtered by the user?

Hi all,

I need to average the number of IDs in the data, that is, if the user chose a data period in the filter, I need to show this average according to the data filter entered, as long as it is a business day.

Example: If the user chooses a period of one week in the filter, I need to calculate the average per business day of that period.

I have a workday field that has “Yes” or “No” .

How to make the average of this field calculated?

avg(distinct_countif({id_customer {jorney} = ‘E’ ) ,[my_date], workday = ‘Y’)


1 Like

Hello @July, we may need to slightly alter my suggestion to get the function to work exactly how you want, but I have a few ideas. What visual are you wanting to display this in?

I think the best route may be to do some nested LAC-W functions and then filtering the visual by the partition.

avgOver(distinctCountOver({id_customer}, [{date}, {jorney}], PRE_AGG), [{date}], PRE_AGG)

If you are wanting to only get customer ids for items where jorney = ‘E’, we can either partition the distinctCountOver function by the jorney field and the date field, then filter by date. Now, if we cannot apply the filter based on Jorney and we need a way to account for workday, we may want to utilize an ifelse statement, then use the output to run our function above. Something like this:

selectCustomerId = ifelse({workday} = 'Y' AND {jorney} = 'E', {id_customer}, NULL)

Then the above function can be re-written like this:
Avg Per Business Day = avgOver(distinctCountOver({selectCustomerId}, [{date}], PRE_AGG), [{date}], PRE_AGG)

You may even want to remove the date field from the partition for the avgOver function. Test out both ways to see which returns the data how you are expecting. This should lead you to your desired output. I’ll mark this as the solution, but please let me know if you have any further questions!

1 Like