
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
Sum(Aggr(If(Sum([Planned Hours]) > 0, 1, 0), [Pva Macro], [Pva Emp Name]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I can confirm Sunny Talwar expression works

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank you! that worked. I was missing a parenthesis when I typed it in initially..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or this for expression without Aggregation
Count({<[Planned Hours] -= {0}>}[Pva Emp Name])
Or
Count({<[Planned Hours] = {'>0'}>}[Pva Emp Name])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 -

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- « Previous Replies
-
- 1
- 2
- Next Replies »