Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggr function count

Hello !

I need to count how many address i've visited before a specific hour for each person. This hour is dynamic, it change for each person.

Data:

Date, ID_PERSON, id_address, hourVisited

26/09, 1, 1, 03:30

26/09, 1, 2, 03:30

26/09, 2, 3, 01:30

26/09, 3, 4, 03:32

26/09, 4, 5, 09:32

And other table:

Date, ID_PERSON, GoalHour

26/09, 1, 04:00

26/09, 2, 01:00

26/09, 3, 01:30

26/09,4, 08:00

So i need to count id_address which hourVisited < GoalHour. My key between this 2 table is ID_PERSON.

In this case, i will count these id_address:

1,2

I've made this expression:

= aggr(count(if(time(hourVisited,'hh:mm:ss') < time#(GoalHour,'hh:mm'), id_address)), ID_PERSON)

This in a pivot table with ID_PERSON as dimension. Doesn't work.

Any suggestions on expression or modelling?

Thanks !

Morandi

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

The problem was format of each date.

hourVisited - i've transformed by other field and used time. Example:

date(date(date, 'YYYY/MM/DD hh:mm:ss'),'hh:mm:ss')


GoalHour - i've just imported. So it was a String.


Formula :

count(if((time#(hourVisited,'hh:mm:ss')<GoalHour,  id_address))


If transformed to time() work too.


Many thanks !

Morandi

View solution in original post

3 Replies
maxgro
MVP
MVP

in a chart with ID_PERSON as dim

try

count(if(hourVisited<GoalHour,  id_address))

jyothish8807
Master II
Master II

Hi Wagner,

In first table you have two entries for ID_person =1 with two different ID_Address, but in table two you have only one entry for Goal hour for iD_person=1 ?How can we identify  for which Id_address goah hour is that?

Regards

KC

Best Regards,
KC
Anonymous
Not applicable
Author

The problem was format of each date.

hourVisited - i've transformed by other field and used time. Example:

date(date(date, 'YYYY/MM/DD hh:mm:ss'),'hh:mm:ss')


GoalHour - i've just imported. So it was a String.


Formula :

count(if((time#(hourVisited,'hh:mm:ss')<GoalHour,  id_address))


If transformed to time() work too.


Many thanks !

Morandi