Need to get latest record — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Need to get latest record

Received Response
223
Views
14
Comments
Jack
Jack Rank 4 - Community Specialist

Hi

i have created a report and getting results as

C1    C2           C3

1      march31  aaa

1      April 1     bbb

but i need report data as  with max date

C1C2       C3

1 April1  bbb

But when i add C3 column in report i am getting 2 records even i add max date group by c1 and C3. Can any one help

Thanks

«1

Answers

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Its is not clear of what max date you want it!! is it from your warehouse or just in the report based on the criteria.

    You decide the logic to get the max date and create a report calling as A then on the current report filter on the date column and use report A.

  • Jack
    Jack Rank 4 - Community Specialist

    Hi

    on report i am keeping max date

  • Joel
    Joel Rank 8 - Analytics Strategist

    HI Manu

    you'll need to do something along the lines of C2 = MAX(C2) in your filter criteria.

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Use this filter on date

    "Time"."Date" = max(rmax("Time"."Date"))

    if this is not working then use a column with exp  max(rmax("Time"."Date")) with above filter

  • Jack
    Jack Rank 4 - Community Specialist

    Hi

    This is helpful.Let me provide more details of my request

    C1    C2           C3

    1      march31  aaa

    2          April 1   aaa

    1      April 1     bbb

    now i need to get


    2   April    aaa,  i need to get lastet date for aaa , if aaa is not available then need to get bbb with latest date

    Thanks

    2     
  • Joel
    Joel Rank 8 - Analytics Strategist

    Then formula needs to be C2 = MAX(C2 BY C3)

  • Jack
    Jack Rank 4 - Community Specialist

    C2 = MAX(C2 BY C3)
    , THIS is brining direct max date not taking C3 into consideration,

    when aaa and bbb is available we need to get max date bsed on aaa , if aaa is ot avaiable then we need to get bbb with latest date

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Good to know.

    mark your self and close this post

  • Jack
    Jack Rank 4 - Community Specialist

    C2 = MAX(C2 BY C3)
    , THIS is brining direct max date not taking C3 into consideration,

    when aaa and bbb is available we need to get max date bsed on aaa , if aaa is ot avaiable then we need to get bbb with latest date

    C1              C2             C3

    1                April 1       aaa

    1                March31   bbb

    2                 April 2     aaa

    3                 March30  bbb

    4                 April4       aaa

    i need to get as

    1        April           aaa

    2        April2         aaa

    3       MArch30     bbb

    4        April4          aaa

    but when i used    C2 = MAX(C2 BY C3) i am getting only 1 records with max date, actually i need max date(C2) based on C3

    4  April4   aaaa

    can you please suggest

  • Joel
    Joel Rank 8 - Analytics Strategist

    Your original description wasn't clear.

    Change it to C2 = MAX(C2 BY C1)