topic Re: Aggregated function with two counts or sum and count in App Development

Aggregated function with two counts or sum and count

Applicable88 — Wed, 20 May 2020 20:12:04 GMT

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!

Re: Aggregated function with two counts or sum and count

jwjackso — Wed, 20 May 2020 22:01:20 GMT

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.

Re: Aggregated function with two counts or sum and count

Applicable88 — Fri, 22 May 2020 11:53:56 GMT

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.