How to solve the need for aggregation in a calculated dimension?

[#1]

Hi there:

I'm trying to create a calculated dimension based on a check on multiple underlying records.

source records:

Project Status

1 Assigned

1 Released

1 Transferred

2 Assigned

2 Locked

2 Released

2 Transferred

3 Assigned

3 Created

3 Transferred

As you can see, each project has multiple statuses.

I would ike to make a new dimension with the following rules:

Active --> if project has a status 'Released' but no 'Locked'

Non active --> if project has a status 'Locked' or no 'Released'

Result then should be :

Project Activation

1 Active

2 Non Active

3 Non Active

And of course we should be able to select all Active projects or non-active projects etc..

What is the best way to achieve this?

[#2]

Seems you already got the answer to it!:)
Just use an if condition in the script as follows:
if(Project=1, 'Active','Non Active") as Flag_Project_Activation
Now you can use this Flag_Project_Activation in the front end to make selections and appropriate Status will be selected..

1 Released

2 Locked

2 Released

Here Project status is Locked as well as released?

Which Activation category will this Project fall under?

[#3 · Accepted solution]

It is probably better to determine the actie/non-active status during the loading of the data. But the following could be a calculated dimension

=if (substringcount(aggr(Concat(Status),Project),'Released')=1,if (substringcount(aggr(Concat(Status),Project),'Locked')=1,'Non-Active' ,'Active'),'Non-Active')

in a table it would look like

Project	Activation	=aggr(Concat(Status),Project)	Sum (Cnt)
			65
1	Active	AssignedReleasedTransferred	9
2	Non-Active	AssignedLockedReleasedTransferred	26
3	Non-Active	AssignedCreatedTransferred	30

Hope this answers your question

[#4]

Thanks Jan,

Your suggestion works great!