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’
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
my formula works fine like this
If “Dia” is already a date, you may not need to use parseDate
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.
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
)
)
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.
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
One more option, if this is direct query, is to use dataset parameters.
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”