topic How to use 'Count' in an expression with 'Above' in QlikView

How to use 'Count' in an expression with 'Above'

Mon, 11 May 2015 15:12:30 GMT

Hello,

I need to count consecutive words in a list to show in a diagram.

So, if on the current line the text is 'Call' and in the above line the same text is in the same column the count needs to be raised by one.

On the selection in the table below the count should be 1.

TimeStamp	CallStatus
12:22:30	Call
12:22:00	Call
12:20:00	Connect
12:19:50	Call

I've been trying to get the result by using the 'Above' function. But as soon as i add a Count function, the editor shows an error in the expression.

This is what i've come up with:

Count (If (If (CallStatus = 'Call', 1, 0) * If (Above (CallStatus, 1, 1) = 'Call', 1, 0), 1, 0))

Anyone got an idea how to solve this ?

Regards,

Gerko Klooster

Re: How to use 'Count' in an expression with 'Above'

m_woolf — Mon, 11 May 2015 16:25:32 GMT

Maybe you can do this in script. Sort the table by timestamp and use peek to look at the CallStatus of the previous record. Create a new field (CallFlag) and set it to 1 if current callstatus is Call and previous callstatus is Call. Then count the CallFlags.

Re: How to use 'Count' in an expression with 'Above'

Tue, 12 May 2015 00:46:00 GMT

you can try using the peek function to find out the previous value of the column.

Count (If (CallStatus = peek('CallStatus') AND CallStatus= 'Call', 1, 0))