Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Help with Set Analysis on Pivot Table

I have the following data (column names in first row)

Employee Name, Timesheet Date, Charge Code, Account Name, Amount

Joe Smith, 11/10/2011, 0010, Labor - Direct, 1000

Joe Smith, 11/10/2011, 0010, Labor - Haz, 400

Joe Smith, 11/10/2011, 0010, Labor - Post, 400

Joe Smith, 11/10/2011, 0015, Labor - Direct, 2000

Joe Smith, 11/17/2011, 0010, Labor - Direct, 2500

Joe Smith, 11/3/2011, 0010, Labor - Direct, 1200

Joe Smith, 11/3/2011, 0010, Labor - Haz, 400

Joe Smith, 11/3/2011, 0010, Labor - Post, 400

Steve Jones, 11/10/2011, 0015, Labor - Direct, 1700

Steve Jones, 11/03/2011, 0015, Labor - Direct, 2000

I want to show in a table whenever an employee charges more than one Account Name on a given Timesheet Date. I've been working on this and can't seem to get the combination of COUNT and set analysis correct to show this. Records where there is only one account name for a given timesheet date should not be shown.

I currently have this in a pivot table, but would be open to a Straight Table or even Table Box if that would help.

Thanks

1 Solution

Accepted Solutions
chematos
Valued Contributor II

Re: Help with Set Analysis on Pivot Table

You could use this expression to calculate the Employee Name dimension to get only the Employees with that condition:

if(aggr(Count(Distinct Charge Code),Employee Name, Timesheet Date)  >1,Employee Name)

4 Replies

Re: Help with Set Analysis on Pivot Table

HI

     Can you able to say what you expected result..

     I tried like below. EmployeeName as Dimension, expression: if(Count(Distinct AccountName) >1,sum(Amount))

Not applicable

Re: Help with Set Analysis on Pivot Table

Sure, the expected result would be a table with the following rows (headers in first row):

Employee Name, Timesheet Date, Charge Code, Account Name, Amount

Joe Smith, 11/10/2011, 0010, Labor - Direct, 1000

Joe Smith, 11/10/2011, 0010, Labor - Haz, 400

Joe Smith, 11/10/2011, 0010, Labor - Post, 400

Joe Smith, 11/10/2011, 0015, Labor - Direct, 2000

This indicates that in this record set the only person with more than one Charge Code on a given Timesheet Date is Joe Smith on the 11/10/2011 timesheet. He charged to both 0010 and 0015 in the same week. In the full dataset, there almost certainly will be more than one person and probably more than one timesheet for each person where there are more than one Charge Code in a timesheet date.

Rick

chematos
Valued Contributor II

Re: Help with Set Analysis on Pivot Table

You could use this expression to calculate the Employee Name dimension to get only the Employees with that condition:

if(aggr(Count(Distinct Charge Code),Employee Name, Timesheet Date)  >1,Employee Name)

Not applicable

Re: Help with Set Analysis on Pivot Table

Hey, I knew that AGGR would come in useful one day. Now I just need to read up on what it actually does.

Thanks José

Community Browser