topic Re: Problem with Pivottable (latest Time and Date) in QlikView

Problem with Pivottable (latest Time and Date)

newqlikie — Thu, 13 Dec 2012 08:45:01 GMT

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.

Re: Problem with Pivottable (latest Time and Date)

nirav_bhimani — Thu, 13 Dec 2012 09:01:42 GMT

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

Re: Problem with Pivottable (latest Time and Date)

newqlikie — Thu, 13 Dec 2012 09:07:01 GMT

Hi,

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

Regards,

NewQlikie

Re: Problem with Pivottable (latest Time and Date)

Thu, 13 Dec 2012 09:07:53 GMT

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

Re: Problem with Pivottable (latest Time and Date)

Thu, 13 Dec 2012 09:10:20 GMT

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

Re: Problem with Pivottable (latest Time and Date)

newqlikie — Thu, 13 Dec 2012 09:16:40 GMT

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

Re: Problem with Pivottable (latest Time and Date)

nirav_bhimani — Thu, 13 Dec 2012 09:24:45 GMT

You can use this expression in sort

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

Hope this will resolve ur problem.

Regards,

Nirav BHimani

Re: Problem with Pivottable (latest Time and Date)

Thu, 13 Dec 2012 09:43:43 GMT

Hi,

see the attached file..

Regards,

Chandra

Re: Problem with Pivottable (latest Time and Date)

sujeetsingh — Thu, 13 Dec 2012 10:04:10 GMT

Very nice Chanred.......

For date list box you can use expression as

date#(datetime)

Re: Problem with Pivottable (latest Time and Date)

newqlikie — Thu, 13 Dec 2012 10:05:07 GMT

Thanks Chandra.

that works perfectly.

Regards

NewQlikie