Cryptic subject but hoping my example will help.
I have this formula:
=If Not(IsNull([test])) Then "Rep.Period" ElseIf(IsNull([test])) Then If([vRevYear]=[vReportingYear]-1 And [vRevMonth]=12) Then "Rep.Period" ElseIf ([vRevYear]=[vReportingYear]-1 And [vRevMonth]=[vReportingMonth]) Then "LY Period" Else "Other"
I have a list with accounts with month and period.
Now I have selected year 2020 month 01 and I want to compare it with 2019 01.
However not every account has an amount in 2020 01.
In that case I want 2019 12 to be the “Re.Period”
In case if there is no 2020 01 it works well.
However in case there is 2020 01 it gives 2020 01 as “Rep.Period” AND 2019 12 aswell.
Looking at the code I can see why it could go wrong but how do I solve it??
ps: the variable test is a sum on YTD amount of 2020 01.
So if it does not find it it will return Null
rpinxt (BOB member since 2019-01-23)