topic Help with Set Analysis on Pivot Table in QlikView

Help with Set Analysis on Pivot Table

Mon, 13 Aug 2012 18:27:01 GMT

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

Re: Help with Set Analysis on Pivot Table

MayilVahanan — Mon, 13 Aug 2012 19:21:35 GMT

Can you able to say what you expected result..

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

Re: Help with Set Analysis on Pivot Table

Mon, 13 Aug 2012 19:26:24 GMT

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

Re: Help with Set Analysis on Pivot Table

chematos — Mon, 13 Aug 2012 20:18:01 GMT

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)

Re: Help with Set Analysis on Pivot Table

Tue, 14 Aug 2012 12:58:16 GMT

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é