Qlik sense - Count the range in table

[nich_olas]

Hi,

I would like to know how can I count the range for status. Based on the example below, the result will be 1 IN, 2 OUT, 3 IN, 2 OUT. And the avg will be 2 IN and 2 OUT. Is there anyway to do this. Thank you.

Date Time Car Status
1/1/2018 0800H A In
1/1/2018 1000H A Out
1/1/2018 1800H A Out
2/1/2018 1730H A In
3/1/2018 0800H A In
3/1/2018 1700H A In
4/1/2018 0800H A Out
4/1/2018 1600H A Out

[jonathandienst]

How did you get this:

>>the result will be 1 IN, 2 OUT, 3 IN, 2 OUT

from your source data?

[nich_olas]

Hi,

Below is the sample data.

S/N	Date	Time	Period	Car	Status
1	1/1/2018	0700H	AM	A	OUT
2	1/1/2018	1700H	PM	A	IN
3	2/1/2018	0800H	AM	A	OUT
4	2/1/2018	1600H	PM	A	IN
5	3/1/2018	0600H	AM	A	IN
6	3/1/2018	1730H	PM	A	IN
7	4/1/2018	0830H	AM	A	IN
8	4/1/2018	1800H	PM	A	OUT
9	5/1/2018	0930H	AM	A	OUT
10	5/1/2018	1400H	PM	A	OUT
11	6/1/2018	1000H	AM	A	IN

So based on the data, i would like to get the something like this

	Day(s)	IN	OUT
1/1/2018-1/1/2018	1	1	1
2/1/2018-2/1/2018	1		1
2/1/2018-4/1/2018	3	4
4/1/2018-5/1/2018	2		3
6/1/2018-6/1/2018	1	2

I need to find out the range between each IN and OUT or vice-versa.

Then get the Average range for IN and OUT.

[jonathandienst]

It's still not clear how you get from the data to the result. Please explain. The examples are useful to illustrate the explanation, but they are not an explanation by themselves.

[nich_olas]

Hi,

I count the number of IN before next OUT or vice versa. Which mean the no of IN/OUT to the next status change.

1/1/2018, there r 1 In and 1 OUT
On 2/1/2018, it has IN at 1600H and the status remain unchanged till 4/1/2019 at 0830H. Therefore there will be 4 IN.
On 4/1/2018, it has OUT at1800H and the status remain unchanged till 5/1/2018 at 1400H. Therefore there will be 3 OUT.