topic Re: How to change date format in QlikView

How to change date format

Thu, 09 Mar 2017 18:14:51 GMT

Hello all,

My data currently has the date listed in the following format:

Jan 29, 2017 22:01:46 EST

I'm trying to change it so it only has 1/29/2017 with no time stamp. How would I accomplish this?

Also, I'm trying to set an expression for when the date is within the last 14 days, display all the data with severity = 'high'

thank you in advance!

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 18:18:14 GMT

May be like this

LOAD Date(Floor(TimeStamp#(SubField(DateField, ' EST', 1), 'MMM DD, YYYY hh:mm:ss'))) as Date

Re: How to change date format

vishsaggi — Thu, 09 Mar 2017 18:25:35 GMT

Try this?

Replace the date with you datefield name.

= Date(Date#(Replace('Jan 29, 2017 22:01:46 EST', Right('Jan 29, 2017 22:01:46 EST', 12), ' ') , 'MMM DD, YYYY'), 'MM/DD/YYYY')

= Date(Date#(Replace('Datefield', Right('Datefield', 12), ' ') , 'MMM DD, YYYY'), 'MM/DD/YYYY')

Re: How to change date format

aarkay29 — Thu, 09 Mar 2017 18:31:50 GMT

Above Sunny's expression for Date

Date(Floor(TimeStamp#(Trim(SubField(DateField, ' EST', 1)), 'MMM DD, YYYY hh:mm:ss'))) As Date

For Severity Flag

if(Date>=Date(today()-14),'High') as [Severity Flag]

Re: How to change date format

Thu, 09 Mar 2017 18:51:20 GMT

This works, but there's also 'EDT', so all those dates with EDT are blank, how do I fix that?

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 18:53:08 GMT

May be try this:

Date(Floor(TimeStamp#(SubField(DateField, ' E', 1), 'MMM DD, YYYY hh:mm:ss'))) as Date

Re: How to change date format

aarkay29 — Thu, 09 Mar 2017 19:00:09 GMT

Try This

Date(Floor(TimeStamp#(Trim( Left(DateField,Len(DateField)-3)), 'MMM DD, YYYY hh:mm:ss')))

Re: How to change date format

Thu, 09 Mar 2017 19:04:42 GMT

hm, severity flag isn't working. this is how i was thinking to try it, but it didnt work for me:

if(New_first_discovered>=Date(today()-14) AND New_Severity = 'High'

Re: How to change date format

aarkay29 — Thu, 09 Mar 2017 19:10:53 GMT

what are you exactly trying to achieve and also Is New_first_discovered in Date format ??

Re: How to change date format

Thu, 09 Mar 2017 19:36:37 GMT

This worked for the date issue, thanks

Re: How to change date format

Thu, 09 Mar 2017 19:44:57 GMT

New first discovered is the date field.

its complicated. I have two qvd's i'm pulling information from. New data and Closed data

for the chart, i want a stacked bar graph, with the severity on the x axis (high and low) and the stacks to show New data and Closed data.

I also want to break down the x axis by time frame (0-14 days, 15-30 days, etc) please see picture attached.

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 19:48:58 GMT

You want this High Low flag in comparison to Today's date?

Re: How to change date format

Thu, 09 Mar 2017 19:50:28 GMT

yes, in comparison to the date it was created.

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 19:59:03 GMT

So the below date in comparison to today?

Date(Floor(TimeStamp#(SubField(DateField, ' E', 1), 'MMM DD, YYYY hh:mm:ss'))) as Date

May be this

LOAD *,

If(CreatedDate <= Today() - 14, 1, 0) as Flag;

LOAD Date(Floor(TimeStamp#(SubField(DateField, ' E', 1), 'MMM DD, YYYY hh:mm:ss'))) as Date

YourOtherDataFields

FROM ....;

Now whichever row has Flag = 1, it is the row where CreatedDate is within 14 days of Today(). and where ever it is 0, the created date is older than 14 days from today

Re: How to change date format

Thu, 09 Mar 2017 20:02:04 GMT

so if this is in my load script, how would I create the same pattern for 15-30 days, 31-90, and 91-180+?

and what would I put in my expression?

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 20:04:22 GMT

Same idea....

LOAD *,

If(Today() - CreatedDate <= 14, Dual('0-14 days', 1),

If(Today() - CreatedDate <= 30, Dual('15-30 days', 2),

If(Today() - CreatedDate <= 90, Dual('31-90 days', 3), Dual('91-180+ days', 4)))) as Flag;

LOAD Date(Floor(TimeStamp#(SubField(DateField, ' E', 1), 'MMM DD, YYYY hh:mm:ss'))) as Date

YourOtherDataFields

FROM ....;

Re: How to change date format

Thu, 09 Mar 2017 20:09:53 GMT

okay got it, but then how do i set up my expressions in the bar chart?

Re: How to change date format

sunny_talwar — Thu, 09 Mar 2017 20:11:38 GMT

Seems like you need three dimensions

Dimension

Flag

Severity

ClosedData/NewData Field

Expression

Count/Sum expression

Re: How to change date format

Fri, 10 Mar 2017 16:39:54 GMT

hm, Flag is only pulling 91-180+ days.

so closed/open doesn't have a field. its two documents, one with data from all the open fields, and one document with all the closed fields. How would I use that for a dimension? I'm confused for what I should be counting in the expression

Re: How to change date format

sunny_talwar — Fri, 10 Mar 2017 16:45:47 GMT

I think it is difficult to understand and know what you have. Would you be able to share the app you are working with?