Meals > 100$/Day calculation on Transaction date.

[joshibabu_are]

Hi All,

i want to calculate Meals spend amount >100 per day

Formulae: if an employee spent>=100 in a single day(Transaction date) then sum of  amount.

here in chart i want to show amount 496.24 but its showing 471.22. expression excluding  9.67,15.35 in calculation.

Take 2016-09-2018  as Base transaction date for example

how to write expression,.

i want to write like below  but showing error in expression

Sum({<[Concur.REPORT_ENTRY_EXPENSE_TYPE_NAME]={'Meals – Employee Only'},

Aggr(sum([Concur.REPORT_ENTRY_APPROVED_AMOUNT]),[Transaction Date])={">100"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT])

please find the attached application

Any help would be greatly appreciated.

Thanks,

Joshi

[#2]

Hi Joshi,

you have only 2 values which are >100 (224.34,246.88), whatever the result you got up there is correct. From where you got that 496!?

Regards

[tamilarasu · Accepted solution]

Hi Joshi,

Maybe this?

=Sum(

Aggr(If(Sum({<[Concur.REPORT_ENTRY_EXPENSE_TYPE_NAME]={'Meals – Employee Only'}>}Concur.REPORT_ENTRY_APPROVED_AMOUNT)>100,

Sum({<[Concur.REPORT_ENTRY_EXPENSE_TYPE_NAME]={'Meals – Employee Only'}>}Concur.REPORT_ENTRY_APPROVED_AMOUNT)),[Employee Name],[Transaction Date]))

Check with all possible cases and let me know if any issues.

Note: I have updated my expression again.

[joshibabu_are]

hi Bharath,

Thanks for your time.

here i have values for date 2016-09-18 are

9.67

15.35

224.34

246.88

i want to sum up all 4 values because these amounts are spent in a same transaction date.

Logic:i want to sum amounts which are done in a single day.if sum of single day amounts are <100 those should be avoided in Calculation.

Thanks,

Joshi