Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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))
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
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)
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é