topic Re: Excluding values in AVG not working in App Development

Excluding values in AVG not working

valpassos — Sat, 16 Nov 2024 04:10:06 GMT

Hi community,

I have a duration measure in days that tells me how much time a task took to be completed, in average, as follows:

AVG(Interval([Task Completed Date] - [Task Started Date])

But there are certain tasks that take less than half a day (<0.5 of duration) to be completed, and I want to exclude these tasks from my AVG duration. How can I accomplish that?

I have tried the following:

AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

This is supposed to exclude tasks whose duration are less that 0.5 days, but it isn't working as expected - the AVG is still counting them.

What am I doing wrong?

Thank you for your help!,

Lisa

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 15:18:02 GMT

The set analysis Syntax looks correct.

How are you creating the column %Task_Duration

I would check the value,type of that and double check

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 16:17:17 GMT

Hi,

The %Task_Duration field is calculated in the model in this way:

Interval([Task Completed Date] - [Task Started Date])

I'm trying to show the AVG on a KPI, not a table.

Thanks,

Lisa

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 16:22:16 GMT

thats fine.

check the data in column and column type.

i ran a test on the set analysis to make sure with some dummy data and it works

mine Avg({<Duration-={"<0.5"}>}Duration)

your AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

so it must be something in data level

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 16:39:22 GMT

If a put the expression in a table, with or without SA, the value is the same

Duration with SA:

AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

Duration without SA:

AVG(%Task_Duration)

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 16:40:23 GMT

Forgot the printscreen:

Re: Excluding values in AVG not working

dwforest — Wed, 06 Nov 2019 16:44:12 GMT

set analysis is calculated once per chart (or table). NOT once per row, switch to IF
AVG(IF(%TaskDuration >0.5,%TaskDuration))

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 16:45:15 GMT

your previous comment said "I'm trying to show the AVG on a KPI, not a table."

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 16:55:37 GMT

I've put the expressions in the table just for testing purposes

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 16:56:59 GMT

share your output in KPI. table a lot depends on the dimensions used etc.

And also include an extract of the duration column if possible

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 17:05:55 GMT

Hi @dplr-rn ,

The output in KPI is the same that I've showed in the table. The value for the average task duration is showing 5.28, with or without set analysis.

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 17:08:06 GMT

whats the data type for %Task_Duration?

Also Put values for that into a striaght table and check values

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 17:15:38 GMT

Not sure if this is the result we want:

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 17:19:23 GMT

It's number type.

Yeah that's what I did, I put the expressions in a straight table, like I've shown in the printscreen.

Re: Excluding values in AVG not working

dplr-rn — Wed, 06 Nov 2019 17:21:16 GMT

not the expressions. the actual values of the duration column

Re: Excluding values in AVG not working

valpassos — Wed, 06 Nov 2019 17:36:15 GMT

But the duration is defined as Interval([Task Completed Date] - [Task Created Date]), and that's what I have in the table, wrapped around with an AVG aggregation to show values. So those are the actual values.