topic complex count in QlikView

complex count

Wed, 05 Jun 2013 17:36:05 GMT

Hi,

Let's imagine I have this data structure :

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 — Wed, 05 Jun 2013 17:49:55 GMT

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

see attached example

Wed, 05 Jun 2013 17:58:01 GMT

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

Fri, 07 Jun 2013 12:16:59 GMT

help, anyone ?

flipside — Fri, 07 Jun 2013 13:09:04 GMT

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

Fri, 07 Jun 2013 13:17:05 GMT

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