Average of the last 10 days

Good morning friends!
Can anyone help me with a calculated field that calculates the average of the last 10 days according to the date set in the control?

Could you do this?

avg(ifelse(dateDiff(${control_date},{date})<=10,{value},NULL))

avg(ifelse(dateDiff(${control_date},{date})<=10,{value},NULL))

What would that date field be?

Do you have a date field?

Those of the “Dia” and “Mês” in control are of type String.

If you want to calculated the last 10 days you need to have rows that tell you what date certain values are.

You can look to parseDate to cast a string to a date.

I have this date field in the base “transaction_datetime” which is in the format ‘yyyy/mm/dd hh:mm:ss’

When trying to create the field this error appears.

can you move the cursor to the red highlighted line under “avg” to see the error message?
instead of putting the “count(nsu)” in calculated field, you may just put nsu in here. and do the “count” in Field wells when building the visual

1 Like


“We encountered an error trying to save the calculated field. Try to create it again.”

my formula works fine like this
image

If “Dia” is already a date, you may not need to use parseDate

1 Like

I was able to use two date fields in datediff but I have this other problem of not returning the NSU field count value.

it shows error because the ifElse condition is checking whether each row of record is <=10 or not. You cannot add an aggregation function inside it.
image

I assume nsu is a dimension? and the count(nsu) will show either one or zero.
Then you may use below formula. Show as one or zero

avg(
ifelse(
    dateDiff({Order Date},${controlDate},"DD")<=10, 1, 0
    )
)
1 Like

I did as requested but there is a problem, when the date is chosen in the control the data will only refer to that date so it is not possible to calculate the average of the last 10 days. It remains the same value in the average and in the day.

MicrosoftTeams-image (9)

hi @Andre_Augusto_Vieira

Yes- that is the expected behavior as the filter will filter out the data calculated in the table/last 10 days avg.

Not sure if we can support here calculating based on the control date + allowing user to further filter out dates. Shouldn’t everything be based on the control date?

thanks,
Ramon

1 Like

One more option, if this is direct query, is to use dataset parameters.

1 Like

it happens because the date range filter was applied, you can solve it by disable date range filter and the data need to be filtered by using “Controls”