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: 
vdcastro
Partner - Contributor III
Partner - Contributor III

Analysis by hour

Hello everyone,

i am starting with Qlikview so i don't have yet a lot of experience with this tool and right now i have a problem:

I have this data set:

I am trying to do an analysis by hour. I want the ID_Person with the max records by hour by day in a group.

I tried to create a straight table with dimensions Id , Date, Hour, Group,

and an expression "=num(sum({$<ID={'Active'}>}#Count),'#.##0')" to obtain the "Sum" of records of each ID but the result was this table:

1,

IDDateHourGroupSum
12016-01-0110134
22016-01-01101345
42016-01-01101456
32016-01-0111136
52016-01-01102100
62016-01-01102145
72016-01-02142130

What i was expecting was this:

IDDateHourGroupSum
42016-01-01101456
32016-01-0111136
62016-01-01102145
72016-01-02142130

I'm not getting only the max row by hour by day.

Can someone help me?

Thanks a lot

9 Replies
avinashelite

try like this

=aggr(max(num(sum({$<ID={'Active'}>}#Count),'#.##0')),Date,Hour,Group)

Anonymous
Not applicable

Hi Vitor,

Try this:

=Num(max(aggr(sum({$<ID={'Active'}, Hour = {"=max(aggr(sum({$<ID={Active}>}#Count),Date,Group))"}>}#Count),Date,Group)),'#.##0')


Or you can flag it on Script.


Kind regards!

effinty2112
Master
Master

Hi Victor,

                    Is this what you want?

Group Date Hour TopID TopSum
4 456
12016-01-01104456
12016-01-0111336
22016-01-01106145
22016-01-02147130

The last two columns are these expression:

TopID : FirstSortedValue(ID,Aggr(Sum(-Sum), Group, Date,Hour,ID))

TopSum: FirstSortedValue(Sum,Aggr(Sum(-Sum), Group, Date,Hour,ID))

vdcastro
Partner - Contributor III
Partner - Contributor III
Author

Hello Andrew,

thank by your answer.

This is what i want. E tried to reply your expressions but i am losing values of hours (ex: for day 2016-01-01 i should have 24 hours but i am getting just 2)

i made this:

$(sum_values) = sum({$<ID={'Active'}>}#Count) // to get the sum


dimensions : group, date, hour

expressions:

TopID: FirstSortedValue(ID,-Aggr($(sum_values), group, date,hour,id)) (your expression has an error)


TopSum has an error too but i didnt fix it yet

vdcastro
Partner - Contributor III
Partner - Contributor III
Author

Hello,

Thanks by your answer.

are you recommending me to remove my dimensions or keep them?

Greetings

Vítor

vdcastro
Partner - Contributor III
Partner - Contributor III
Author

I tried again and i think it will work with the TopSum column.

TopSum: FirstSortedValue($(sum_values),-Aggr(Sum(-$(sum_values)), Group, Date,Hour,ID))


and i think the error is here: FirstSortedValue($(sum_values)  <--


but i can be completely wrong

effinty2112
Master
Master

Hi Victor,

Keep your dims in and try this

TopID : FirstSortedValue(ID,Aggr(-sum({$<ID={'Active'}>}#Count), Group, Date,Hour,ID))

The solution I gave earlier worked from the table you posted in your query. This is the same expression but with

sum({$<ID={'Active'}>}#Count)


replacing 


Sum(-Sum)


Good Luck

Andrew

vdcastro
Partner - Contributor III
Partner - Contributor III
Author

Thank you very much by your help

avinashelite

you need to keep the dimension otherwise AGGR will not work