Use AGGR in an dimension to return one result from an group

[tinkerz1]

Hi,

I have an ID field that has a status field with 5 outcomes (Open,Accepted,Draft,Closed,'Blank')

The result I would like to return is just one status, the formula is to go through in order of the above list and

count the number of ID's per status.

If the count of the ID is zero move through the above list and stop when the count of the ID is not zero.

Then return that status. ie. Status='Draft'

example.

=if(aggr(count (ID),[STATUS]='Open')>0,
if([STATUS]='Open',[STATUS])
,[STATUS])

But this wont work if the row does not have an ID of Draft.

Thanks.

[Anonymous]

Okay, what issue you faced?

Could you elaborate more what exactly you want?

[Anonymous]

For this only you can try this?

=if(Count({<Status={'Open'}>} ID)>0, Count({<Status={'Open'}>} ID), '-') as FName

[#4 · Accepted solution]

Hi Neil,

You can acheive this by creating a flag table like below.

flag:

left Join(input)

load * inline [

Status,value

Open,1

Accepted,2

Draft,3

Closed,4

Blank,5

];

And you can join the input table with this flag table then you will get the ID, Status,value. Then in the next step we can take min(value) based on the group by ID. Then we will get the desired result.

Below is the script

input:

load * inline [

ID,Status

1,Open

1,Draft

2,Accepted

2,Draft

3,Closed

3,Open

];

flag:

left Join(input)

load * inline [

Status,value

Open,1

Accepted,2

Draft,3

Closed,4

Blank,5

];

minvalue:

Inner Join

load ID, min(value) as value

Resident input group by ID;

And you take the input table in table box. And you will get the desired result.

Thanks,
Sreeman