Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
try like this
=aggr(max(num(sum({$<ID={'Active'}>}#Count),'#.##0')),Date,Hour,Group)
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!
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))
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
Hello,
Thanks by your answer.
are you recommending me to remove my dimensions or keep them?
Greetings
Vítor
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
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
Thank you very much by your help
you need to keep the dimension otherwise AGGR will not work