Sum only current year balance amount by employee ID

AppSheet AppSheet Q&A
1

Hi There

I am trying to put a filter YEAR([CLAIM DATE (RECEIPT DATE)])=YEAR(TODAY()) in the below expression but there seems to be always an error. Anyone knows where to put this filter so as every year the Blance Amount will only cum the current year amoutn by employee ID.

[MC ENTITLEMENT]-((SUM(
SELECT(Medical Chit[REIMINBURSE AMOUNT],([EMPLOYEE ID]=[_THISROW].[EMPLOYEE ID]))
))+[REIMINBURSE AMOUNT])

desmond_lee_0-1705912243759.png
desmond_lee_0-1705912243759.png1305×501 56.4 KB

2

What is the formula that gives the error? And what that error is?

3

I addded the year expression behind and the AND before the SUM. It shows

desmond_lee_0-1705912977460.png
desmond_lee_0-1705912977460.png784×282 22 KB

4

The AND() is in wrong place. You need to use it inside of the SELECT(). Something like.. SELECT(Medical Chit[Reimimburse amount],AND(…))

1 Like
5

Thanks for the sugguestion. I manage to use the IF() function like below

[MC ENTITLEMENT]-((SUM(
SELECT(Medical Chit[REIMINBURSE AMOUNT],([EMPLOYEE ID]=[_THISROW].[EMPLOYEE ID]))
))+IF(YEAR([CLAIM DATE (RECEIPT DATE)])=YEAR(TODAY()),[REIMINBURSE AMOUNT],0))