topic Re: Aggr function count in QlikView

Aggr function count

Anonymous — Fri, 26 Sep 2014 14:44:03 GMT

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

Re: Aggr function count

maxgro — Fri, 26 Sep 2014 14:49:38 GMT

in a chart with ID_PERSON as dim

try

count(if(hourVisited<GoalHour, id_address))

Re: Aggr function count

jyothish8807 — Fri, 26 Sep 2014 14:59:35 GMT

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

Re: Aggr function count

Anonymous — Fri, 26 Sep 2014 15:01:58 GMT

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