Oracle Analytics Cloud and Server

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

Can i use MIN and Max Function together?

Received Response
41
Views
7
Comments
Rank 5 - Community Champion

I am trying to use the  a min and max functions on this case statements. What I want is that when the first case statement is true, I would like to return the min date and when the second case statement is true return the max date then group everything BY "Details"."PNumb"). I have tried it as shown below but getting syntax errors. any suggestions on where I might be going wrong?

(MIN(CASE WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

ELSE (CASE WHEN "Details"."PNumb" = "Details"."PI" AND "JOB"." Name" ="JOB"."Name"

THEN MAX("XX"."Date") END) END) BY "Details"."PNumb")

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

    Hi ForSly,

    For any given record, "JOB"." Name" will always equal "JOB"."Name", so your WHEN and ELSE conditions are identical. (Or is that leading blank space intentional?  If so, read on.)

    The first condition will always catch anything that the ELSE statement is intended to catch, since both contain "Details"."PNumb" = "Details"."PI".

    Your ELSE condition is more restrictive, so put that first, and reverse the MIN/MAX operations.

    pastedImage_0.png

  • Rank 5 - Community Champion

    What do you mean by reversing my MIN/MAX?- I have made some corrections- changed the job name definitions. Would you be kind enough to show me how  I can write it without getting an error?

    (MIN(CASE WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

    ELSE (CASE WHEN "Details"."PNumb" = "Details"."PI" AND "JOB"." Name" LIKE '%-DIE' OR "JOB"." Name" LIKE '%-DEW'

    THEN MAX("XX"."Date") END) END) BY "Details"."PNumb")

  • Rank 6 - Analytics Lead

    I don't know the details of your data, but I was talking about something like this:

    CASE WHEN "Details"."PNumb" = "Details"."PI" AND ("JOB"." Name" LIKE '%-DIE' OR "JOB"." Name" LIKE '%-DEW') THEN MAX("XX"."Date" by "Details"."PNumb")

    WHEN "Details"."PNumb" = "Details"."PI" THEN "XX"."Date"

    ELSE cast(NULL as DATE) END

    Your original statement has no condition for when "Details"."PNumb" <> "Details"."PI", so I added the ELSE NULL.   If "Details"."PNumb" is always equal to "Details"."PI", you don't need it in the conditions.

    I don't see why you would need to nest the whole thing with a MIN statement, unless I'm missing something.  I moved the By "Details"."PNumb" clause into your MAX fnx.

  • Rank 5 - Community Champion

    I  wanted to be able group both min and max results set by "Details"."PNumb"). Does it make it any difference if I start with max ?

  • Rank 6 - Analytics Lead

    Your statement is testing a set of "PNumb"s against a set of conditions and extracting a specific date based on those conditions.  That looks like the output is one date per "PNumb". 

    There must be more to it if you want to then group by PNumb.  Not enough information for me to understand your requirement.

  • Rank 5 - Community Champion

    my requirement is to calculate the offers we extended to employees , basically if someone applied for a job in January and we extended an offer to them in February, my requirement is that when I create metric to show offers extended in January, I should show all offers even those extended in February

  • Rank 5 - Community Champion

    my requirement is to calculate the offers we extended to employees , basically if someone applied for a job in January and we extended an offer to them in February, my requirement is that when I create metric to show offers extended in January, I should show all offers even those extended in February

Welcome!

It looks like you're new here. Sign in or register to get started.