topic Calculate Variable in Table in QlikView

Calculate Variable in Table

Thu, 07 Jul 2011 08:31:59 GMT

Hi,

i have this problem to calculate a variable.

In My Data Base I have 2 Date: DATE_1 and DATE_2. View example

The date format is yyyymmdd.

I import them and trasform using the formula:

MAKEDATE(LEFT([DATE_1],4),MID([DATE_1],5,2),RIGHT([DATE_1],2)) AS DATE_1_CORRECT,
MAKEDATE(LEFT([DATE_2],4),MID([DATE_2],5,2),RIGHT([DATE_2],2)) AS DATE_2_CORRECT,
LEFT([DATE_1.],4) as YEAR_DATE_1,
LEFT([DATE_2],4) as YEAR_DATE_2.

Now a i want to create a variable "DIFF" of the difference of 2 date and anylize the mean (AVG) of DIFF in days for each ID.

I want this mean for each iD only if "DIFF" variable is > 5 or < 200 and year_date_1 = 2010,2011 and year_date_2 = 2010,2011

I think to use DATE_1_CORRECT - DATE_2_CORRECT.
I need only a table to analyize this mean. Is not important to create a variable when i import the data base.
In the example i make a table of mean of differnce for each id but without conditions...

Who can help me?

Thank You

Calculate Variable in Table

brenner_martina — Thu, 07 Jul 2011 08:42:37 GMT

Hi, Marco,

only a little help: your expression in script is nice but not needed, QlikView has an interpreter function for dates and numbers:

Date means the format you want to store

Date# means in which format date is in the source stored

Date(Date#(DATE_1,'YYYYMMDD'),'DD-MM-YYYY') As DATE_1_CORRECT

Num(Num#('1.234,56','#.##0,00'),'#,##0.00') As Number

Calculate Variable in Table

Thu, 07 Jul 2011 08:57:55 GMT

Thank you!

I've already try this and it's ok!