count aggr sum

[Anonymous]

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

[sunny_talwar]

May be this:

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

[Anonymous]

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)

[sunny_talwar · Accepted solution]

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

Data used:

Table:

LOAD * INLINE [

    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

];

[vvvvvvizard]

I can confirm Sunny Talwar expression works

[Anonymous]

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

[Anil_Babu_Samineni]

Or this for expression without Aggregation

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

Or

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

[sunny_talwar]

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.

[Anonymous]

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 -

[sunny_talwar]

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]))