In expression, when there are no data for selected year, pic data from year-1.

[#1]

Hi

I have a dimension with numbers, 2010,2011, 2012 that works as year field. The problem is that the data are read by qv as text (in a tablebox qv puts the numbers to the left)

I use it in a expression, but when there are no data for 2012 I would like to set it to use the data for previous year. Therefor I convert it to date format In the script by

Date

(Date#(YEARFIELD, 'YYYY'), 'YYYY')  as NewYearField

But then in the application, when I put NewYearField - 1 (and select 2012) I only get 40908. How can I fix this? Or should I solve the basic problem with lack of data on some other way? The basic problem is that: When there are no data for selected year, do the caluculation with the data from prevoius year or in worst case the year before that.

[#2]

Hi,

The problem is that NewYearField is a date so for 2012 the value in NewYearField is 01/01/2012, when you use  NewYearField - 1 , the result value '40908' is the number equivalent to the date 31/12/2011. I can think on two solutions for this problem:

1) You can use: Year( NewYearField ) -1

2) In your script you can convert your YEARFIELD as number using:

     num#( YEARFIELD ) as NewYearField

      and you can use: NewYearField - 1 to get the previous year

Hope this helps

Regards!

[#3 · Accepted solution]

Hello hofstedt,

     I think you are in a little mess with dates and types:

     If you want to work with "Number of Year", this is a number (2011, 2012, 2013...) and only a number. You can substract 1 and then you have the year before.

     If you want to work with dates, you need a full date (year, month and day). In this case you can use MakeDate function to build a valid date, for example MakeDate(YEARFIELD) or MakeDate(Num(YEARFIELD)), month and day defaults to 1 in this example. In this case you will have a Date type, and you can use the date functions, like AddMonths(), etc.

Hope this helps.

[#4]

Thanks, I will try the makedate. It opens for the use of a calendar...