Count the values by Block and find the Max and Avg

[nich_olas]

Hi all,

I've the following data.

I need to count the Status for each block of IN and OUT, and find out the Max and Avg IN and OUT for each Car. Below is the example for Car 1. Each session is 0.5 day. 

Is there anyway that i can count the status for each block in Qliksense?

Please help me as i cannot resolve this for a week already. Really appreciate the help or advise given.

Thank you.

[Kushal_Chawda]

@nich_olas  how you are deciding the block?

[nich_olas]

Hi Kush, based on the status change, from IN to OUT or OUT to IN. 

Based on the example, there are 3 blocks of IN and 3 blocks of OUT. 

im looking at Rangesum, but not really sure how to use it. Especially when to stop the count and reset it to zero in order to start next block. 

[Kushal_Chawda]

@nich_olas  Would you be able to share sample data?

[Neymar_Jr]

Check if this works -

Alt(
if(
if(isnull(above(total[Status])),Status,above(total[Status]))=Status
,rangesum(above(total if(if(isnull(above(total[Status])),Status,above(total[Status]))=Status,.5,0),0,rowno(total))),.5)
,.5)

[nich_olas]

Hi Kush,

Attached is the sample data. Really hope that you can help. Thank you.

[nich_olas]

Hi Neymar_Jr, thanks for the replied. The formula doesn’t work. The value will not be the expected result after first block. Please see the result below based on my example. (Sorry that I reply using my phone)

IN-0.5

IN-1

IN-1.5

OUT-0.5

OUT-2 (from here onward the value is wrong)

IN-0.5

IN-2.5