12 Replies Latest reply: Feb 14, 2017 2:09 PM by Maria Harmening

# count aggr sum

i'd like to aggregate data 2 categories and then sum hours within that aggregation.  then if the sum of the hours is greater than zero, i'd like to count how many there are within the aggregation.  this is the formula i have, but I'm missing something..

count({<{\$(aggr(sum([Planned Hours]),[Pva Macro],[Pva Emp Name])={">0"}}>} [Planned Hours])

please do not attach qvf files because I cannot load them on my machine.

Thanks,

Maria

• ###### Re: count aggr sum

May be this:

Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Macro], [Pva Emp Name]))

• ###### Re: count aggr sum

that didn't work.  basically I want to have a result set that looks like this

macro name one

maria 30

tom 50

jane 200

macro name two

dick 10

john 0

Jason 100

then I want to count how many people there are per macro with hours greater than zero, so my final result would be

marco name one - 3 (3 people have hours in this macro)

marcro name two - 2 (2 people have hours in this macro)

• ###### Re: count aggr sum

Created a sample in QlikView (Qlik Sense will work the same way) and it seems to be working

Data used:

Table:

Pva Macro, Pva Emp Name, Planned Hours

One, maria, 30

One, tom, 50

One, jane, 200

Two, dick, 10

Two, john, 0

Two, Jason, 100

];

• ###### Re: count aggr sum

thank you!  that worked.  I was missing a parenthesis when I typed it in initially..

• ###### Re: count aggr sum

I can confirm Sunny Talwar expression works

• ###### Re: count aggr sum

Or this for expression without Aggregation

Count({<[Planned Hours] -= {0}>}[Pva Emp Name])

Or

Count({<[Planned Hours] = {'>0'}>}[Pva Emp Name])

• ###### Re: count aggr sum

Anil -

This is great idea, the only problem is that if there are more than one rows for [Pva Macro],[Pva Emp Name] combination and check needs to be for Sum([Planned Hours]) instead of just Planned Hours, then your above approach is not going to work.

• ###### Re: count aggr sum

shouldn't I just be able to then take the Average, mode, and median of the result set?

Avg(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Macro], [Pva Emp Name])))

I'm getting -

• ###### Re: count aggr sum

Are you looking to get 2.5 here? Try like this:

Avg(Aggr(Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Macro], [Pva Emp Name])), [Pva Macro]))

• ###### Re: count aggr sum

I believe this works.  thank you.  now I'd like to make a histogram of the results.  I was thinking I'd make a dimension like this..

ValueList('0-2','3-5','6-8','9-11','12-14','15-17','18-20','21-23','24-26')

but then how would I get the correct values as the measures?  would I chart the results from above with an if statement to match the intervals in the value list?

• ###### Re: count aggr sum

What are these intervals here? These are related to the calculated avg above?

• ###### Re: count aggr sum

so let's say that the original question I had yielded these results

marco a - 3 people have hours in this macro

macro b - 2 people have hours in this macro

macro c - 6

macro d -17

macro e - 10

marco f - 3

macro g - 2

macro h - 6

macro i -17

macro j - 10

now I want to chart that 2 macros had between 0-2 people, 2 macros had between 3-5 people, 2 macros had between 6-8 people...etc..