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