topic Re: Century date in QlikView

Century date

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We are having date as

(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ day(Today()-1) = 1161109

Here we need the month start like = 1161101

Please let met me know how to get this.

Thanks..

Re: Century date

rubenmarin — Thu, 10 Nov 2016 15:37:26 GMT

Hi naresh, so it's only set the first day of the month?, wouldn't be easier to set a fixed day '1'?:

(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ 1

Re: Century date

Anil_Babu_Samineni — Thu, 10 Nov 2016 16:21:03 GMT

You may try this way,

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ MonthStart(day(Today()))-1

Re: Century date

Oleg_Troyansky — Thu, 10 Nov 2016 16:23:59 GMT

Sorry to discourage you guys, but this formula is wrong... Use Ruben's suggestion...

Re: Century date

Anil_Babu_Samineni — Thu, 10 Nov 2016 16:27:11 GMT

Oleg, Why So it is wrong? Would i expect reason. so that i can gain bit more

Re: Century date

sunny_talwar — Thu, 10 Nov 2016 16:32:59 GMT

Although, I agree with Oleg that Ruben's method is probably the best, but you have incorrect order of things. This might make more sense:

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ Day(MonthStart(Today()))

Here you are first calculating MonthStart for today and then finding what day it is. Fortunately, MonthStart is always 1 and hence Ruben's method is more straight forward

Re: Century date

Oleg_Troyansky — Thu, 10 Nov 2016 18:26:16 GMT

Yeah, what Sunny said... To be absolutely precise, you should also subtract 1 from Today() as the rest of the formula does, but not from the final result as you suggested:

=(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100+ Day(MonthStart(Today()-1))

Re: Century date

MarcoWedel — Thu, 10 Nov 2016 23:06:31 GMT

Hi,

maybe you could create your century date as dual value using your required format, yet still keeping the underlying numerical value to be able to apply date functions to it:

LOAD *,
    Dual((Div(Year(Date),100)-19)&Date(Date,'YYMMDD'),Date) as CenturyDate,
    Dual((Div(Year(Date),100)-19)&Date(Date,'YYMM01'),MonthStart(Date)) as CenturyDateMonthStart;
LOAD Date(RecNo()+1) as Date
AutoGenerate Today();

regards

Marco

Re: Century date

rwunderlich — Thu, 10 Nov 2016 23:55:49 GMT

Yet another option

=num#(Date(Addmonths(MonthStart(today()),-1900*12),'YYYMMDD'))

-Rob

Re: Century date

johnw — Fri, 11 Nov 2016 00:01:13 GMT

I'm very happy you've kept the underlying numeric value of CenturyDate the same as a regular date. If it were my application, I'd also want to be able to make it as easy as possible to use date functions on any century date, and get back something in century date format. So to expand on your idea, I'm thinking a formatting variable.

SET cd = dual(year($1)-1900&date($1,'MMDD'),$1);

table1:
LOAD $(cd(Date)) as CenturyDate;
LOAD date(recno()+1) as Date
AUTOGENERATE today();

Then we can $(cd(monthstart(CenturyDate))) for the first of the month, still a date, but in the correct format, and we can do that in a chart or in the script as desired.