topic Re: Simplify the formula in QlikView

Simplify the formula

tamilarasu — Thu, 30 Apr 2015 10:17:58 GMT

Hi,

Can any one simplify the formula please.

VDate is a variable and the values can be 201506

=MakeDate( Left(Purgechar(vDate,Chr(39)),4),Right(Purgechar(vDate,Chr(39)),2))

Thank you.

Re: Simplify the formula

sunny_talwar — Thu, 30 Apr 2015 10:22:24 GMT

If you just need to convert your variable into a date you can use this:

=Date#(vDate, 'YYYYMM')

But if you need a day as well then you will have a big formula like this:

=MakeDate(Year(Date#(vDate, 'YYYYMM')), Month(Date#(vDate, 'YYYYMM')), 2)

HTH

Best,

Sunny

Re: Simplify the formula

sunny_talwar — Thu, 30 Apr 2015 10:23:49 GMT

Another look at your formula, why are you purging Chr(39)? You variable doesn't seem to have any single quotes in it?

Re: Simplify the formula

ankitaag — Thu, 30 Apr 2015 10:31:18 GMT

What is the value in your vDate variable, do you have special characters which you are removing by using purgechar.

Re: Simplify the formula

tamilarasu — Thu, 30 Apr 2015 10:32:59 GMT

I Can't edit variable as it was already created in a application. I just adding an expression in a chart by using this variable.

Sorry actually the variable contains year and month like '201505', so I am using purgechar syntax.

Re: Simplify the formula

tamilarasu — Thu, 30 Apr 2015 10:34:36 GMT

Yes Ankita. The variable contains single quotes at the starting and end of the string.

Re: Simplify the formula

vikramv — Thu, 30 Apr 2015 10:35:33 GMT

Sunindia is Right, No need to use purge char,Use like this

=MakeDate( Left('2001/11/11',4),Right('2001/11/11',2))

If your vDate = '2001/11/11'

Thanks,

Vikram.

Re: Simplify the formula

Thu, 30 Apr 2015 10:35:35 GMT

=Date(MakeDate( Left(vDate,4),Right(vDate,2)),'MMYYYY')

Re: Simplify the formula

sunny_talwar — Thu, 30 Apr 2015 10:37:54 GMT

This worked for me if you don't need a day:

=Date(Date#(vVar, Chr(39)&'YYYYMM'&Chr(39)), 'MMM-YYYY')

Re: Simplify the formula

ankitaag — Thu, 30 Apr 2015 10:44:44 GMT

If you want to create the whole date then

=MakeDate(mid($(vDate),1,4),mid($(vDate),5,6),01)

or if you just want YYYY-MM then

=Date(Date#($(vDate),'YYYYMM'),'MM/YYYY')

Re: Simplify the formula

tamilarasu — Thu, 30 Apr 2015 14:07:56 GMT

Hi Ankita,

Your solution also working good. But I am unable to mark two correct answers and i am allowed to mark yours as helpful.

Re: Simplify the formula

ankitaag — Thu, 30 Apr 2015 17:35:59 GMT

Thankyou