topic How to get max time stamp from first 3 dates in QlikView

How to get max time stamp from first 3 dates

Wed, 25 Nov 2020 16:16:04 GMT

I want to get the max time stamp of first 3 days .I am using aggr(max(Datetime),Date) but I want to restrict to first 3 days

Data	Expected output
7/13/2016 9:45:26 PM	7/13/2016 9:45:26 PM
7/13/2016 8:45:26 PM	7/12/2016 9:48:38 PM
7/12/2016 9:48:38 PM	7/11/2016 9:45:26 PM
7/12/2016 9:45:26 PM
7/11/2016 9:45:26 PM
7/11/2016 8:45:26 PM
7/8/2016 9:45:26 PM
7/8/2016 8:45:26 PM
7/7/2016 9:45:26 PM
7/7/2016 8:45:26 PM
7/7/2016 11:45:26 AM
7/7/2016 10:45:26 AM
7/6/2016 9:45:26 PM
7/6/2016 8:45:26 PM

Re: How to get max time stamp from first 3 dates

Thu, 14 Jul 2016 16:00:24 GMT

This expression gives the desire result

=if(Max(TOTAL DATE ,3) <=DATE,

aggr(

max

(

Datetime

)

,DATE)

)

Re: How to get max time stamp from first 3 dates

sunny_talwar — Thu, 14 Jul 2016 18:35:02 GMT

Is this for a calculated dimension? May be this:

Aggr(If(Max(TOTAL <Date> Datetime, 3) >= Datetime, Datetime), Date, Datetime)

Re: How to get max time stamp from first 3 dates

Thu, 14 Jul 2016 19:13:37 GMT

That didn't work.it displays more than 3 and not the max of each day

Re: How to get max time stamp from first 3 dates

Anonymous — Fri, 15 Jul 2016 12:32:30 GMT

Yes, it's fine.

What is your question? Do you want to do this in script?

Re: How to get max time stamp from first 3 dates

effinty2112 — Fri, 15 Jul 2016 14:31:19 GMT

Hi Krishna,

Is this any good?

Date	Max TS
13/07/2016	9:45:26 pm
12/07/2016	9:48:38 pm
11/07/2016	9:45:26 pm

It's a straight table with a hidden first expression which I'll unhide:

=Date(Floor(Timestamp#(TS)))	=Rank(Max(Date(Floor(Timestamp#(TS)))))	Max(Time(Timestamp#(TS)))
13/07/2016	1	9:45:26 pm
12/07/2016	2	9:48:38 pm
11/07/2016	3	9:45:26 pm

In the dimensions limit tab set it to show the smallest three values for the most recent dates. If you set it to show the largest three you will get data on the three oldest dates. You can hide this column by selecting the option in the Presentation tab.

Cheers

Andrew