Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

complex count

Hi,

Let's imagine I have this data structure :

ProjectPeriodeStatus

1

1

A
12B
21A
22A
31B
32C












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 !

Tags (2)
1 Solution

Accepted Solutions

Re: complex count

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

see attached example


talk is cheap, supply exceeds demand
5 Replies

Re: complex count

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

see attached example


talk is cheap, supply exceeds demand
Not applicable

Re: complex count

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 Anumber of projects
A1
B0
C0




Not applicable

Re: complex count

help, anyone ?

flipside
Valued Contributor II

Re: complex count

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

Not applicable

Re: complex count

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

Community Browser