count with conditions

[dcorte]

Hi! I'm new to Qlik Sense and I'm having a problem:

I'm trying to create a KPI to count the number of different values for the field "Data" for which i have two values ('Entrada' and 'Saída') in the field "Condição". For example:

Município	Condição	Data
São Paulo	Entrada	07/01/2020
São Paulo	Saída	07/01/2020
São Paulo	Entrada	21/01/2020
São Paulo	Saída	21/01/2020
São Paulo	Entrada	04/02/2020
São Paulo	Saída	04/02/2020
São Paulo	Entrada	18/02/2020
São Paulo	Saída	18/02/2020
São Paulo	Entrada	03/03/2020
São Paulo	Entrada	31/03/2020
São Paulo	Entrada	14/04/2020
São Paulo	Entrada	28/04/2020

In this case, the KPI would be '4', which is the count of different "Data" values ('07/01/2020','21/01/2020','04/02/2020' and

'18/02/2020') for which I have two different values in the "Condição" field.

Anyone with a solution?

[GaryGiles · Accepted solution]

I think this is what you are looking for:

=Count({$<Data={"=count({$<[Condição]={'Entrada'}>} Data)>0 and count({$<[Condição]={'Saída'}>} Data)>0"}>} distinct Data)

[Saravanan_Desingh · Accepted solution]

Try this,

=Count(DISTINCT{<Data=P({<Condição={'Entrada'}>})> * <Data=P({<Condição={'Saída'}>})>}Data)

[dcorte]

Thanks, guys!!

Both suggestions worked perfectly.

But thinking about further calculations and a more optimized database model, would it be possible to organize my data like this?

Município | Entrada | Saída | Data
São Paulo | 1 | 1 | 07/01/2020
São Paulo | 1 | 1 | 21/01/2020
São Paulo | 1 | 1 | 04/02/2020
São Paulo | 1 | 1 | 18/02/2020
São Paulo | 1 | 0 | 03/03/2020
São Paulo | 1 | 0 | 31/03/2020
São Paulo | 1 | 0 | 14/04/2020
São Paulo | 1 | 0 | 28/04/2020

This way I wouldn't have duplicated values for the field "Data" and it would help me a lot. Is there an easy way to do this using the script?

Thank you in advance!