topic Re: RE:Dynamic date varies in QlikView

RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 16:35:16 GMT

Hi all,

i have small issue on Date issue

REQ:

My date format field: "Wednesday - 02-24-2016"

Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

it varies day to day

so in this case

the first description max length is 9 which is wednesday

so i need to get the format only DD-MM-YYYY

my max lenght 9 only so while coming to wednesday its alrdy 9 and there is space and - symbol.

here the day coming dynamically....

So how can i do this?

Thanks in advance.

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 16:37:34 GMT

You can try this may be:

Date(Date#(Right(DateField, 10), 'MM-DD-YYYY'), 'DD-MM-YYYY') as DateField

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 16:44:23 GMT

ok bro..let me try and let u knw

am getting output as 016032

but i need the format below

YYYYMMDD

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 16:46:27 GMT

016032? For Wednesday - 02-24-2016? or you tried another date?

To get another format, you can change the Date() function format:

Date(Date#(Right(DateField, 10), 'MM-DD-YYYY'), 'YYYYMMDD') as DateField

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 16:53:48 GMT

hi bro,

the issue is exctly..... the abve exp is working very much fyn.

As that date field "Wednesday - 02-24-2016" associated wit other field so we need to get the

output YYYYMMDD but we need to remove abve date from the sheet?

is it possible? if we remove the date out givng as null value?

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 16:55:43 GMT

As that date field "Wednesday - 02-24-2016"    associated wit other field so we need to get the

output YYYYMMDD      but we need to remove abve date from the sheet?

is it possible?    if we remove the date out givng as null value?

I am not able to interpret these three statements above. Can you elaborate?

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 17:02:49 GMT

Are you doing this in the script or front end?

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 17:03:59 GMT

front end only....nt in script

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 17:05:21 GMT

Then this as the dimension isn't working?

Date(Date#(Right([VIEW DATE], 10), 'MM-DD-YYYY'), 'YYYYMMDD')

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 17:10:33 GMT

sunny that field we are usng as measure ....so user dnt wat to c full date format as see just as yyyyddmm

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 17:12:53 GMT

I don't see a difference in viewing it as a dimension vs. measure? All you want to see is the date in YYYYDDMM format? I guess have you tried -> Date(Date#(Right([VIEW DATE], 10), 'MM-DD-YYYY'), 'YYYYMMDD') as measure?

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 17:26:51 GMT

exctly sunny......

we hve taken as measure abve expression...same..

wen we tried with as dimension abve expresion will givng exact date format.

but wen we use as Measure it givng as nulll?

what might be the reason?

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 17:29:00 GMT

Unless you have a field in a dimension which can uniquely show all your dates (new dates in YYYYMMDD), you will see Null(). The purpose of expression is to show aggregation. You can do Avg(Date) if you don't have a unique dimension, but I am not sure how will that be of any help.

Re: RE:Dynamic date varies

Chanty4u — Tue, 22 Mar 2016 17:34:42 GMT

Thanks sunny...

i will chk it tommorw and let you know. if i need anything

Thanks for the info ....

Best

Suresh

Re: RE:Dynamic date varies

sunny_talwar — Tue, 22 Mar 2016 17:36:03 GMT

No problem

Re: RE:Dynamic date varies

Chanty4u — Mon, 28 Mar 2016 09:03:22 GMT

Hi sunny,

as a dimension if i use

the previous logic

Date(Date#(Right(AIR_DATE, 10), 'YYYY-MM-DD'), 'YYYYMMDD')

it is timing out the sheet which iam trying to run

throws an error as "calculation timed out"

Is there any other alternative?

Re: RE:Dynamic date varies

sunny_talwar — Mon, 28 Mar 2016 10:41:07 GMT

Unless you provide more details of what you are trying to do or share a sample, it would be difficult to give much advice man. Is AIR_DATE coming from a table which is not linked to anything else in that particular chart?

Re: RE:Dynamic date varies

Mon, 28 Mar 2016 12:09:02 GMT

Hi chanty,

try this

Date(Date#(KeepChar('Wednesday - 02-24-2016','0123456789'),'MMDDYYYY'),'YYYYMMDD')