What is wrong with this syntax??
=sum(if(Clearing_AUGDT >'31/Jan/2014' and [Posting Date_BUDAT]<='31/Jan/2014' , ([Amount in LC_DMBTR])))
Why its not working?
You can try this
=Sum(if((Date(Clearing_AUGDT,'DD/MMM/YYY')>Date('31/Jan/2014','DD/MMM/YYY)) and (Date([Posting Date_BUDAT],DD/MMM/YYY')<=Date(31/Jan/2014',DD/MMM/YYY')), ([Amount in LC_DMBTR]),0))
Its better to change it into date format in script, so that it works properly in all tables and expression.
Date(Clearing_AUGDT,'DD/MM/YYYY') as Clearing_AUGDT,
Date([Posting Date_BUDAT],'DD/MM/YYYY') as Posting Date_BUDAT
After this try the same script you used.Hope this help.
From the field names (BUDAT), it seems you are working with SAP data. The dates in SAP are anyway trickier to work with. Check from qvd in what format the date is being stored and has the QV app recognised it as a date format.
One of the initial tests can be to check in a text box if a Num operation on the date field gives the correct number corresponding to that date.