Hi all, I have 2 VC called Current Fiscal Sta...

expressions
(William Stewart) #1

Hi all, I have 2 VC called Current Fiscal Start and Current Fiscal End =IF(MONTH(NOW())>=04,CONCATENATE(“01/04/”,YEAR(NOW())),CONCATENATE(“01/04/”,YEAR(NOW()-1)))

and

=IF(MONTH(NOW())<=03,CONCATENATE(“31/03/”,YEAR(NOW())),CONCATENATE(“31/03/”,(YEAR(NOW())+1)))

Respectively to give (today) 31/04/2018 & 31/03/2019

I now want to use the date output of the VC in a report expression: <<IF(AND([Date]>=[Current Fiscal Start],[Date]<=[Current Fiscal End]),[Expense Total],“

“)>>

This gives the error cannot compare [Date] with LIST. Even if I just do <<[Current Fiscal Start]>> it doesn’t show the output date. Is this because of the formula in the VC? - perhaps reports cant perform the formula??

Thank you

(Suvrutt Gurjar) #2

Hi @William_Stewart,What is the column type of those

2 VCs please?

(Suvrutt Gurjar) #3

Hi @William_Stewart, I did test with VCs cast as Date columns in reports and it works well.

A couple of things you may wish to ensure are - please ensure VCs are of Date types and please ensure your dates formats formatted through VC and being used in the app are of same format (dd/mm/yyyy in your case?)

Hope this helps.