topic Re: Calculations with date as variable in QlikView

Calculations with date as variable

yura_ratu — Fri, 21 Feb 2014 08:53:04 GMT

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

Re: Calculations with date as variable

Gysbert_Wassenaar — Fri, 21 Feb 2014 09:05:56 GMT

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.

Re: Calculations with date as variable

yura_ratu — Fri, 21 Feb 2014 09:11:09 GMT

i make it in this way

let vToday = today();

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

is it correct?

Re: Calculations with date as variable

Gysbert_Wassenaar — Fri, 21 Feb 2014 09:19:36 GMT

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)

Re: Calculations with date as variable

yura_ratu — Fri, 21 Feb 2014 09:58:25 GMT

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

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