Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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

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

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

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
Not applicable

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

Okay cool. I shall wait for the sample App.

Cheers,

DV

www.QlikShare.com

Not applicable

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

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
Not applicable

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

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

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

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

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

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
Not applicable

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

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