topic Group by within an Expression in QlikView

Group by within an Expression

Fri, 24 Feb 2012 16:42:15 GMT

Ok, there has to be an easy way to do this....

I have a data set containing person name, company name, and company revenue. Since there are multiple people per company, the company revenue figures are repeated several times. I need a report showing number of people, number of companies, and total revenue. How do I set this up in a pivot table to avoid the double counting?

Thanks.

Group by within an Expression

onetwothree123 — Fri, 24 Feb 2012 16:45:54 GMT

draab,

Try using DISTINCT in your expression

Group by within an Expression

Fri, 24 Feb 2012 16:51:23 GMT

I know DISTINCT would work with the company names -- i.e., =count (distinct Company_Name)

But it's perfectly possible that there will be several companies reporting the same revenue, which I should think would cause problems. Can you give an example of code that would work for the Revenue figures?

Thanks.

Group by within an Expression

Fri, 24 Feb 2012 16:52:44 GMT

could you upload an example of your data?

Group by within an Expression

Fri, 24 Feb 2012 16:53:07 GMT

Hi Draab,

If you do not have a revenue figure per person then if you include person as a dimension you will get the revenue repeated across that dimension.

Prehaps use comapny as the dimension, and then do a distinct count of people and a sum of revenue as expressions.

Thanks,

James

Group by within an Expression

swuehl — Fri, 24 Feb 2012 16:56:24 GMT

Something like

=sum( aggr(only(Revenue), Company))

should do then.

Group by within an Expression

Fri, 24 Feb 2012 17:07:14 GMT

That did it. Thanks much. Never saw 'only' before, but I'm very rusty.