Master Calendar

[vireshkolagimat]

Hi All,

I got the below script while creating the master calendar. Could somebody please explain it.

On what basis we taking the multiplier. Year is multiplied by 1000 and month by 100 & day nothing.

Year(Temp_Date) * 10000 + Month(Temp_Date) * 100 + Day(Temp_Date)  as [DateKey],

Regards,

Viresh

[Frank_Hartmann]

actually you are just determining the digits of your datefield  by multiplying.

e.g.

Year(Now()) * 10000 =  2017 * 1000        = 20170000

Month(Now()) * 100  = 08 * 100               = 0800

Day(Now())                                               = 20

20170000 + 0800 + 20 = 20170820

[swuehl · Accepted solution]

Day shows at max 2 digits, month shows at max 2 digits.

So by multiplying Month with 100 and Year with 100*100, you ensure that Year, Month and Day values keep seperated when adding up the values, to ensure that each DateKey value represents an unambiguous Date value.

[effinty2112]

Hi Viresh,

This script will create dates in the format YYYYMMDD.

20th Aug 2017

Year(20th Aug 2017)*10000 =      20170000

Month(20th Aug 2017)*100 =                 800

Day(20th Aug 2017) =                               20

                                                       20170820

Cheers

Andrew

[swuehl]

You should be able to alternatively use

Num#(Date([Temp_Date],'YYYYMMDD')) as [DateKey]

You need the Num#() to not only format the text representation of the temp date as desired, but to create a numeric value that matches the format.

QlikView Date fields

[vireshkolagimat]

Hi Stefan,

thanks for the clarification.

Regards,

Viresh