Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mharm
Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

Re: count aggr sum

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

Capture.PNG

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

];

12 Replies
MVP
MVP

Re: count aggr sum

May be this:

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

mharm
Contributor

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)

MVP
MVP

Re: count aggr sum

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

Capture.PNG

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

];

Highlighted
vvvvvvizard
Contributor III

Re: count aggr sum

I can confirm Sunny Talwar expression works

mharm
Contributor

Re: count aggr sum

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

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

MVP
MVP

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.

mharm
Contributor

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 -

MVP
MVP

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