topic Re: Set Analysis not returning the desired results in App Development

Set Analysis not returning the desired results

wcoleman0908 — Wed, 11 Oct 2023 15:41:15 GMT

I've been unsuccessful in getting this stuburn code to work correctly. What I'm attempting is to return is all records where:

1) The [project status] is open

2) Return those projects where NO projects have any values with a [completion status] equal to 'In_Prog'

The problem is that it returns projects but includes those projects with 'In_Prog' data values. My task is to Complete those projects that are open that are no longer 'In_Prog'

My set Analysis is as follows:

Count(aggr(Count({<[Project Status]={'Open'},[Completion Status]-= {'In Progress'} > }distinct [Projects]), [Projects))

I included a sample data file for testing, Any insights would be great

Re: Set Analysis not returning the desired results

BrunPierre — Wed, 11 Oct 2023 18:35:14 GMT

Hi, perhaps this.

=Count({$<[Project Status]={'Open'}, [Projects] -= P({<[Completion Status]={'In_Prog'}>} [Projects])>} DISTINCT [Projects])

Re: Set Analysis not returning the desired results

wcoleman0908 — Wed, 11 Oct 2023 18:50:13 GMT

No that returned In prog project records too. It needs to return a list of projects that have NO 'in_prog' records.

😞

Re: Set Analysis not returning the desired results

BrunPierre — Wed, 11 Oct 2023 19:15:55 GMT

isn't this what you want?

Re: Set Analysis not returning the desired results

coleturegroup — Wed, 11 Oct 2023 19:42:04 GMT

Here is what i'm seeing locally still getting returned

Re: Set Analysis not returning the desired results

coleturegroup — Wed, 11 Oct 2023 19:46:36 GMT

See here:

Re: Set Analysis not returning the desired results

BrunPierre — Wed, 11 Oct 2023 21:06:33 GMT

As a filter pane or calculated dimension use this

=Aggr(Only({$<[Project Status]={'Open'}, [Projects] -= P({<[Completion Status]={'In_Prog'}>} [Projects])>} [Completion Status]), [Completion Status])

Re: Set Analysis not returning the desired results

coleturegroup — Wed, 11 Oct 2023 21:34:21 GMT

Holly cow this is very painful, It still shows, look at project 8892

Re: Set Analysis not returning the desired results

BrunPierre — Wed, 11 Oct 2023 22:10:58 GMT

Add the count measure to filter the result.

Re: Set Analysis not returning the desired results

MayilVahanan — Thu, 12 Oct 2023 02:16:16 GMT

One of the way to achieve this ,

Count({<[Project Status]={'Open'}, Projects-={$(=Concat({<[Completion Status]={'In_Prog'}>}Distinct Projects, ','))}>}DISTINCT Projects)

Re: Set Analysis not returning the desired results

coleturegroup — Thu, 12 Oct 2023 16:12:52 GMT

=count(Aggr(Only({$<[Project Status]={'Open'}, [Projects] = E({<[Completion Status]={'In Progress'}>} [Projects])>} Group, [Completion Status]), [Completion Status]))

Thanks but unfortunately it didn't work as desired. Maybe i need to add more detail to show my results.

I included the updated QVF file, the table below show only show the following results:

Group C should only show project 7300, 8728

Group E should only show project 9035, 9163

Group F should only show project 6753, 8605

The Aggr seems to not be formed properly, can you see the issue?

Thanks in advance for all your help