Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time of Occurance - how can I get the time associated with an item

Trying to report a peak value AND the time that it occured. 

Data is fairly simple:  Time (Date and Time on the hour) and a counter for an event.    Trying to go through the data by Day (Date) and report the maximum counter AND its associated time. 

provided my date-time value is c_Hourly_Time and the counter is c_Count

I can get the maximum value by building a chart using the Date(c_Hourly) for the chart dimension and using Max(c_Count) as the expression.  What I need now is an expression that will return the c_Hourly value associated with this Max(c_Count) record.

8 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Hi,

I think we solve this one using FirstSortedValue() function. I'd love you to help here with the full syntax but I don't completely follow your question. It will be great if you can post a sample QVW file? And I can provide you the complete syntax with explanation.

Meanwhile, I'd encourage you to look at my blog which contains series of tutorials on FirstSortedValue().

http://qlikshare.com/392

I hope this gets you started! Good luck.

Cheers,

DV

www.QlikShare.com

Not applicable
Author

Working on preparing a sample just now.

Norman P. Livermore

MIS Analytics, Enterprise Information Services

SunTrust Bank

Tel: 404.724.3139

Mail Code: GA-ATL-790

303 Peachtree Center Ave., SunTrust Gardens, 3rd Floor, Suite 325

Atlanta, GA 30303

How Can We Help You Shine Today?

IAMDV
Luminary Alumni
Luminary Alumni

Okay cool. I shall wait for the sample App.

Cheers,

DV

www.QlikShare.com

Not applicable
Author

I have created a short example using extract data. (xls)… two columns Date/Time and Counter. In my full application, I have moved the Floor() into the script but for this example, it is in the Dimension definition.

The example project has a table with the two peak counts and the Day. What I need is the associated Time (Date/Time would be acceptable)

Norman P. Livermore

MIS Analytics, Enterprise Information Services

SunTrust Bank

Tel: 404.724.3139

Mail Code: GA-ATL-790

303 Peachtree Center Ave., SunTrust Gardens, 3rd Floor, Suite 325

Atlanta, GA 30303

How Can We Help You Shine Today?

IAMDV
Luminary Alumni
Luminary Alumni

Hi Norman,

My understanding is that you need TimeStamp for the Maximum c_Count field...is this right?

You need this expression:

 

FirstSortedValue(c_Date_Time, -c_Count)

Attaching the same example. Please let me know if you need something else.

Thanks,

DV

www.QlikShare.com

Not applicable
Author

Yes. That would do nicely. What I am trying to produce is a table that lists the Peak value per day and the time (timestamp) that this peak occurred.

Norman P. Livermore

MIS Analytics, Enterprise Information Services

SunTrust Bank

Tel: 404.724.3139

Mail Code: GA-ATL-790

303 Peachtree Center Ave., SunTrust Gardens, 3rd Floor, Suite 325

Atlanta, GA 30303

How Can We Help You Shine Today?

Not applicable
Author

Thanks for your help.

Norman P. Livermore

MIS Analytics, Enterprise Information Services

SunTrust Bank

Tel: 404.724.3139

Mail Code: GA-ATL-790

303 Peachtree Center Ave., SunTrust Gardens, 3rd Floor, Suite 325

Atlanta, GA 30303

How Can We Help You Shine Today?

IAMDV
Luminary Alumni
Luminary Alumni

That's Norman. I'm glad it works. FirstSortedValue() is very powerful function. Please check the above link where I have provide detailed tutorial. Also, please can you mark thread as answered? It helps other users who are looking for similar answer.

Thanks,

DV

www.QlikShare.com