Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlikie
Creator
Creator

Problem with Pivottable (latest Time and Date)

Hello everybody,

I have following problem:

I have a table with following dates:

Customername, Date of order, Time of order

    Customer, Date, Time

    Customer 1, 12.12.2012, 15:24:13

    Customer 1, 12.12.2012, 14:12:11

    Customer 1, 11.12.2012, 19:28:25

    Customer 1, 12.12.2012, 08:05:16

    Customer 2, 06.11.2012, 20:16:48

    Customer 2, 08.11.2012, 17:19:25

    Customer 2, 11.12.2012, 07:16:57

    Customer 2, 13.12.2012, 08:01:21

...

What I need is to create a Pivot-table in which I can see the date and time of the latest order:

Customer 1     12.12.2012     15:24:13

Customer 2     13.12.2012     08:01:21

I tried it with the aggr-function,

max(aggr( max(Time), Date))

but it will not work

Can anybody help me?

I appreciate your help.

Regards,

     NewQlikie

PS: I attached my obove mentioned table.

1 Solution

Accepted Solutions
Not applicable

Hi,

see the attached file..

Regards,

Chandra

View solution in original post

9 Replies
nirav_bhimani
Partner - Specialist
Partner - Specialist

HI,

Copy ur expression and goto sort - check on expression n paste ur exp and choose desc.

Hope this would be solve ur problem.

Regads,

Nirav Bhimani

newqlikie
Creator
Creator
Author

Hi,

no, this is not working. I always get the latest Time irrespective of the latest date.

Regards,

    NewQlikie

Not applicable

Hi,

do concatenate on both Date and time then you will get date in timestamp format.. use max(date(Datetime,'MM/DD/YYY hh:mm:ss). you will get max date as well as latest ordered date.

Regards,

Chandra

Not applicable

Hi,

do concatenate on both Date and time then you will get date in timestamp format.. use max(date(Datetime,'MM/DD/YYY hh:mm:ss). you will get max date as well as latest ordered date.

Regards,

Chandra

newqlikie
Creator
Creator
Author

Hi Chandra,

thanks for your answer. Do you have the possibility to create a sample (see my attached file)? I never worked with concatenate.

Thanks.

Regards,

    NewQlikie

nirav_bhimani
Partner - Specialist
Partner - Specialist

You can use this expression in sort

Max(Date) & max(aggr( max(Time), Date))

Hope this will resolve ur problem.

Regards,

Nirav BHimani

Not applicable

Hi,

see the attached file..

Regards,

Chandra

sujeetsingh
Master III
Master III

Very nice Chanred.......

For date list box you can use  expression    as

date#(datetime)

newqlikie
Creator
Creator
Author

Thanks Chandra.

that works perfectly.

Regards

   NewQlikie