Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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().
I hope this gets you started! Good luck.
Cheers,
DV
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?
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?
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
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?
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?
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