Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
count({<Status={'A'},Periode={1}, Project=P({<Status={'B'},Periode={2}>}Project) >} distinct Project)
see attached example
count({<Status={'A'},Periode={1}, Project=P({<Status={'B'},Periode={2}>}Project) >} distinct Project)
see attached example
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 |
help, anyone ?
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
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