Skip to Content
author's profile photo Former Member
Former Member

How to get the lastest record for each day

Hello,

I meet a problem in my work in BO web intelligence.
I have a set of data, it look like that, for example:

Delivery_Data Forecast_Data Orders
12/16/2015 12/15/2015 10:00:00 AM 100
12/16/2015 12/15/2015 10:30:00 AM 120
12/16/2015 12/15/2015 11:00:00 AM 160
12/13/2015 12/12/2015 10:00:00 AM 300
12/13/2015 12/12/2015 10:30:00 AM 320
12/13/2015 12/12/2015 11:00:00 AM 340

So, I do the forecast one day before the deliver data, every half hour, and the 3rd colums is my total orders at the time I am doing the forecast;

However,now I only want to return the latest record for each delivery data, so the result should be
12/16/2015 12/15/2015 11:00:00 AM 160
12/13/2015 12/12/2015 11:00:00 AM 340

However I can use max to help me return the max forecast time, but the orders measure will aggregate automatically, I use foreach(forcastdate),it doesn;t works, so how can I solve this problem, what function may be useful here;

thanks so much

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Dec 17, 2015 at 06:05 PM

    You can create 2 variables here.

    One to find the max of the Forecast Date

    VAR1 =Max([Forecast_Data]) In ([Delivery_Data])

    Second to get the Order Number for the Max Forecast Date

    VAR2 =[Orders ] Where ([Forecast_Data]=[VAR1])

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.