Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i hope everyone is well.
I created a table in Qliksense.
To understand a aggregated function I just changed the aggregation between sum and count. For me aggregation functions I saw mostly in explanations with variations with sum(sales) was easier to understand than with counts.
For instance I have table with two main columns. One is called 'Serialnumber' (always unique numbers) and the other one is called 'Timestamp' (the time the date the number was created):
Serialnumber | Timestamp |
BF33333 | 06.05.20 |
BF33334 | 06.05.20 |
BF33335 | 06.05.20 |
BF33336 | 06.05.20 |
BF33337 | 06.05.20 |
BF33338 | 07.05.20 |
BF33339 | 07.05.20 |
BF33340 | 07.05.20 |
BF33341 | 07.05.20 |
BF33342 | 07.05.20 |
I add two other columns: count(aggr(count]|[Serialnumber], [Timestamp]) AND sum(aggr(count]|[Serialnumber], [Timestamp])
The first aggregation function gives me the value count of 2. Even if I have ten Serialnumbers in total, does it just counts the two different dates ?
The second aggregations is even more confusing. Since I have the 2 counts in the first functions, when I changed to "sum" it gives me 20. How can this make sense?
When I add a 'distinct" in the second one sum(aggr(count distinct]|[Serialnumber], [Timestamp]) I suddenly get zero!
I hope someone can explain all 3 variations in easy words for me.
Thanks in advance. Stay safe!
The Aggr function creates a virtual table.
I used the following formulas:
count(aggr(count(Serialnumber),Timestamp)) which returns a 2
sum(aggr(count(Serialnumber),Timestamp)) which returns a 10, which is what I expected, not a 20
sum(aggr(Distinct count(Serialnumber),Timestamp)) which returns a 10
The Aggr function returns 2 rows which each have a value of 5. The first formula counts the rows and returns 2. The second formula adds the rows together and returns 10. The third formula is the same as the second formula, distinct is the default and returns 10.
I'm not sure how you got 20 or zero.
The Aggr function creates a virtual table.
I used the following formulas:
count(aggr(count(Serialnumber),Timestamp)) which returns a 2
sum(aggr(count(Serialnumber),Timestamp)) which returns a 10, which is what I expected, not a 20
sum(aggr(Distinct count(Serialnumber),Timestamp)) which returns a 10
The Aggr function returns 2 rows which each have a value of 5. The first formula counts the rows and returns 2. The second formula adds the rows together and returns 10. The third formula is the same as the second formula, distinct is the default and returns 10.
I'm not sure how you got 20 or zero.
Thank you very much! Very good explained. My counts really weren't the same at work. Maybe there is something I missed in the data. When I'm back to office I recheck my data and write you back if I cannot find the mistake myself.
Stay safe.