complex count

[#1]

Hi,

Let's imagine I have this data structure :

Project	Periode	Status
1	1	A
1	2	B
2	1	A
2	2	A
3	1	B
3	2	C

How do I write a formula to calculate the number of projects which are in status B in Period 2 and were in status A in Period 1 ?

Thank you !

[Gysbert_Wassenaar · Accepted solution]

count({<Status={'A'},Periode={1}, Project=P({<Status={'B'},Periode={2}>}Project) >} distinct Project)

see attached example

[#3]

wow thanks

And how would you write it as an expression for a chart, who would have the Status of the first Period as dimension ?

The chart would be :

Last status of projects in status A	number of projects
A	1
B	0
C	0

[#4]

help, anyone ?

[flipside]

Hi,

The expression is exactly as Gysbert has written it. The only extra things you may need to do to display the zero values are:

1) Tick "Suppress When Value Is Null" and "Show All Values" for the Status dimension.

2) Untick "Suppress Zero-Values" on the Presentation tab.

Hope this helps

flipside

[#6]

Well, no

I have put 0 in my exemple because it is what it would be with the data I have given in exemple

it could have been with more data

A 1

B 5

C 3

But I REALIZE now that I just have to put Status in a dimension, and use Gysbert's formula without the Status={'A'}, part