Calculations with date as variable

[yura_ratu]

Hi all.

I have a variable endDate which value is the last day of date range. Now I want to calculate revenue on 7th day before endDate. I've tried

sum({<CurrTransDate={"$(=date($(endDate)-7))"}>}USDSpent)  

but it doesn't work. I suppose it's because of mistakes in expression. What did I do wrong?

Thanks

[Gysbert_Wassenaar]

endDate has to have a numberic value for this to work. A lot of date and time functions return a dual value, meaning a numeric value that also has a text representation. But perhaps endDate is a string value only and you need to use the date# function to create a date from a string value first.

[yura_ratu]

i make it in this way

let vToday = today();

let endDate = date(vToday,'YYYY-MM-DD');

is it correct?

[Gysbert_Wassenaar · Accepted solution]

Yes, that's correct. But what is the default date format? Do you have a line SET DateFormat=.... in your script? What's is the format specified there? Is it the same as the format of the CurrTransDate field? They either have to be the same or you need to specify the correct format in the expression. For example YYYY-MM-DD: sum({<CurrTransDate={"$(=date($(endDate)-7,'YYYY-MM-DD'))"}>}USDSpent) 

[yura_ratu]

Yes, formats were different. Thank you! This expression works

=sum({<CurrTransDate={'$(=date('$(endDate)'-7,'YYYY-MM-DD'))'}>}USDSpent)