Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Creation datetime ==> date

I have lists of customer purchasing information. and I am struggling with purchasing time column.

my column looks like YYYY-MM-DD hh:mm:ss.

I have used date function and I am able to trim out time part and I only have YYYY-MM-DD now.

so the list box that I am seeing looks like this

num  name     date

1      david      2016-02-01

2     hannah    2016-02-01

3     daniel     2016-02-01

4     tanya      2016-02-01

...

...

...

each column is a list box. I guess this makes sense since only the time part was taken out.

but what I want to do is to be able to only see one date by each row. and by clicking each row, I want to be able to filter out all the customers that purchased on that day.

I want my date listbox to look like

2016-02-01

2016-02-02

2016-02-03

...

and another question is that I want to create a bar chart that shows how many records are created in one day.

but right now when I set the dimension as date, multiple 2016-02-01 are on horizontal axis.

Thank you in advance!!

7 Replies
MVP & Luminary
MVP & Luminary

Re: Creation datetime ==> date

Using the Date() function does not cut off the time part. It only changes the display format, not the value. You need to use the Floor() function if you want to remove the time part: Date(Floor(MyDate),'MM/DD/YYYY')


talk is cheap, supply exceeds demand
Partner
Partner

Re: Creation datetime ==> date

How are you trimming the date part of your timestamp? Are you doing in from the script or in the Front-End.

Anyway, have you trying using Floor(Field)? that way QV will only keep the Date part of the timestamp. e.g:

LOAD

    num,

    name,

    Floor(Date) as newDate

FROM....


Could you provide an example of your application?


Regards

Re: Creation datetime ==> date

Hi,

It seems that your date column is coming as text instead of Date.

Try below code while loading data.

Date(Timestamp(Timestamp#(FieldDate,'YYYY-DD-MM hh:mm:ss')),'YYYY-DD-MM') as Date

Regards,

Kaushik Solanki

Not applicable

Re: Creation datetime ==> date

thank you it worked!!!

Not applicable

Re: Creation datetime ==> date

thank you it worked!!!

Not applicable

Re: Creation datetime ==> date

Hi Kaushik,

Thank you so much for your answer.

I want to know more about what exactly Timestamp means and what you mean by date column is coming as text not as date.

Please explain little more I want to know what function the Timestamp has.

and further more, when organization uses shared Date QVD, do they make sure that all values are in there as date values not as text? this is very new to me.

I am asking this since I am curious if the two columns do not merge properly if one is text based and one is date value based even though their values look identical on the surface.

thank you!!

Re: Creation datetime ==> date

Hi David,

What I thought is, Many a times when your data source is flat files or excel, the Date Field doesn't get recognized as Date field, instead it is recognized as text column. So to convert such text into date, we use the #functions.

So in the expression I gave, I tried to convert the timestamp into a timestamp format first and then from that timestamp I tried to get date using Date function.

Regards,

Kaushik Solanki