1

I have a table of travel expenses for analysis.

I would like to create a calculated column with a value for the maximum count of records with a certain category for each employee on any given day.

For example, if the category being reviewed is "dinner", we would like to know what is the maximum number of dinner transactions charged on any given day.

The following custom expression was able to count how many dinner expenses per employee:

count(If([Expense Type]="Dinner",[Expense Type],null)) over ([Employee])

But when trying to get the max count over days, I cant seem to get it to work. Here is the expression used:

Max(count(If([Expense Type]="Dinner",[Expense Type],null)) over ([Employee])) over (Intersect([Employee],[Transaction Date]))

This seems to provide the same answer as the first expression. Any idea on how to get this code to identify the value on the date with the most expenses for each employee?

cookiemnstr247
  • 121
  • 3
  • 14
  • how are you displaying this, or do you just want this in the original table? Do you have a date column? – S3S Oct 17 '17 at 18:04
  • I would like to perform the calculation in the original table (I understand that every record for that employee would have the same value, but that is the intention). Yes, it is the [Transaction Date] value in the second expression. – cookiemnstr247 Oct 17 '17 at 19:02

1 Answers1

1

If i understand your question and comments correctly, you should be able to use intersect.

count(If([Expense Type]="Dinner",[Expense Type],null)) over (Intersect([Transaction Date],[Employee]))

You may need to cast [Transaction Date] as a date if it is an actual DateTime. Otherwise you'd get one for each unique DT.

S3S
  • 24,809
  • 5
  • 26
  • 45