topic Re: In expression, when there are no data for selected year, pic data from year-1. in QlikView

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

Mon, 21 Jan 2013 15:46:53 GMT

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.

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

Mon, 21 Jan 2013 16:46:42 GMT

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!

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

Mon, 21 Jan 2013 16:51:33 GMT

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.

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

Mon, 21 Jan 2013 19:19:36 GMT

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