I want to create an EXPRESSION ! Do help

[#1]

Hello All,

The query here is I have some device which are frequently upgrading and after update they turn to again updating. For In progress the response code is 'NA' and for updated it is '0'.

I have one column with me which shows the date-time latest now I want to pick the

latest response code of a particular device id whether it would be '0' or 'NA' on basis of "com_send_time"  Column .

Scenario is lik this

P.S: I have around 700 device !

device_id	response_code	com_send_time
1852	NA	23-07-2015 13:28
1852	NA	24-07-2015 11:42
1852	0	23-07-2015 13:41
1852	0	24-07-2015 11:54
1852	-	-
2314	NA	24-07-2015 11:41
2314	0	24-07-2015 11:59
2314	-	-

As you can see above 1852 is showing different response code but I just want the count for the latest

i.e  24-07-2015 11:54

[settu_periasamy]

is it possible to post more listed example and the expected output? then it would be easier to find the solution.

[#3]

I am using a gauge which will show the count of devices which are updated i.e response code ='0'

and another gauge for device count having response code='NA'

now what is happening devices are frequently updating and the counting is not proper cause of it so I have a column name com_send_time which shows which response code came from device I jus want that response code to be counted which is latest

device_id	response_code	com_send_time
1852	NA	23-07-2015 13:28
1852	NA	24-07-2015 11:42
1852	0	23-07-2015 13:41

see its a huge table just I croped single device for ex.

now with all this frequent update it should at last count the data of latest which is

24-07-2015 11:42

and 'NA' count should increase as it is showing response code as 'NA' that time.

[sasiparupudi1]

Please try

=Count({<com_send_time={"=aggr(Max(timestamp#(com_send_time,'DD-MM-YYYY hh:mm')),device_id)" }>}response_code)

[#5]

" before =aggr ("=aggr) ??????

[#6]

Showing error if im removing " "

[sasiparupudi1]

can you please paste your expression? I am not sure why you want to remove the quotes as they are required in the set analysis expression

[teiswamsler]

Hi Cyrus

Please take the time and watch this Welcome to the Qlik Sense Community Forum (video) 4:30

It chould help you, help others understanding your current problem.

Remember that others users in here, actually is trying to help you.

/Teis

[#9]

sir the expresion is corect but not performing ,count is on 0 not counting any other expression.

where I can set the response code in expression so that I can see a direct count for update('0') on green for in progress('NA') in yellow

p.s: 0 and NA are response_code

I have applied ur expression sir on the yellow one, see its not responding

[teiswamsler]

Hi Cyrus

In the set analysis you can write in a ekstra set modifier," response_code = {'0'}, ", that will make the expression to count only the lines with response_code = 0 for the given periode.

ex.

Count({< response_code = {'0'},  com_send_time={"=aggr(Max(timestamp#(com_send_time,'DD-MM-YYYY hh:mm')),device_id)" }>}response_code)

/Teis

[#11]

hello teis,

Bro dun knw wats wrong on going it is still on 0 ? can we make any other expression or any script!! It's creating a huge issue now