Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
nich_olas
Contributor
Contributor

Count the values by Block and find the Max and Avg

Hi all,

I've the following data.

bb.png

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. 

aa.png

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.

 

 

 

 

6 Replies
Kushal_Chawda

@nich_olas  how you are deciding the block?

nich_olas
Contributor
Contributor
Author

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
Creator II
Creator II

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
Contributor
Contributor
Author

Hi Kush,

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

nich_olas
Contributor
Contributor
Author

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