topic new to aggregate functions in QlikView

new to aggregate functions

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to figure out how to have a function return the number of times the total pallets for a given date are greater than or equal to 6. I've pasted an example of my data below. Basically, I need it to first sum the number of pallets on each given date and then tell me how many times that total is greater than or equal to 6...in this case, the result should be 2. I think an aggregate function is how I get there but I am extremely new to this and can't seem to get it to work. Help!

ship date	# of pallets
1/1/2016	2
1/1/2016	3
1/1/2016	1
1/1/2016	7
1/2/2016	2
1/2/2016	6
1/3/2016	1
1/3/2016	1
1/3/2016	1
1/4/2016	3

Re: new to aggregate functions

sunny_talwar — Fri, 10 Jun 2016 15:54:49 GMT

May be like this in a text box object

Sum(Aggr(If(Sum([# of pallets]) >= 6, 1, 0), [ship date]))

Re: new to aggregate functions

Fri, 10 Jun 2016 18:28:55 GMT

It worked perfectly. Thank you!

Now to take it a step further...let's say I want it to return the number of times the sum is between 4 and 7. How would I write that?

Re: new to aggregate functions

sunny_talwar — Fri, 10 Jun 2016 18:51:09 GMT

May be like this:

Sum(Aggr(If(Sum([# of pallets]) >=4 and Sum([# of pallets]) <= 7, 1, 0), [ship date]))

Or this should also work:

Count(DISTINCT {<[ship date] = {"=Sum([# of pallets]) >=4 and Sum([# of pallets]) <= 7"}>} [ship date])

and Similarily the expression provided previously can also be replaced by this:

Count(DISTINCT {<[ship date] = {"=Sum([# of pallets]) >=6"}>} [ship date])