topic Re: Analysis by hour in QlikView

Analysis by hour

vdcastro — Thu, 02 Jun 2016 10:44:23 GMT

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:

ID	Date	Hour	Group	Sum
1	2016-01-01	10	1	34
2	2016-01-01	10	1	345
4	2016-01-01	10	1	456
3	2016-01-01	11	1	36
5	2016-01-01	10	2	100
6	2016-01-01	10	2	145
7	2016-01-02	14	2	130

What i was expecting was this:

ID	Date	Hour	Group	Sum
4	2016-01-01	10	1	456
3	2016-01-01	11	1	36
6	2016-01-01	10	2	145
7	2016-01-02	14	2	130

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

Can someone help me?

Thanks a lot

Re: Analysis by hour

avinashelite — Thu, 02 Jun 2016 13:09:46 GMT

try like this

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

Re: Analysis by hour

Anonymous — Thu, 02 Jun 2016 13:26:04 GMT

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!

Re: Analysis by hour

effinty2112 — Thu, 02 Jun 2016 13:50:15 GMT

Hi Victor,

Is this what you want?

Group	Date	Hour	TopID	TopSum
			4	456
1	2016-01-01	10	4	456
1	2016-01-01	11	3	36
2	2016-01-01	10	6	145
2	2016-01-02	14	7	130

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))

Re: Analysis by hour

vdcastro — Thu, 02 Jun 2016 14:27:25 GMT

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

Re: Analysis by hour

vdcastro — Thu, 02 Jun 2016 14:33:53 GMT

Hello,

Thanks by your answer.

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

Greetings

Vítor

Re: Analysis by hour

vdcastro — Thu, 02 Jun 2016 14:50:51 GMT

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

Re: Analysis by hour

effinty2112 — Thu, 02 Jun 2016 15:30:54 GMT

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

Re: Analysis by hour

vdcastro — Thu, 02 Jun 2016 16:18:51 GMT

Thank you very much by your help

Re: Analysis by hour

avinashelite — Fri, 03 Jun 2016 05:22:38 GMT

you need to keep the dimension otherwise AGGR will not work