Oracle Analytics Cloud and Server

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

Display Measure values when contain negative value

Received Response
181
Views
12
Comments
User_JL3CW
User_JL3CW Rank 6 - Analytics Lead

Hi all,

I have a requirement where in my report should display results based on dashboard prompt values.

For example, a dashboard prompt which should contain values "ALL" and "Contain Negative".

When the user selects "ALL" in DB prompt then my report to display all the values (both +ve and -ve measure column values).

If i select "Contain Negative" (if my measure values contain at least one -ve value) then my report should display measure column values (both +ve and -ve values), here if all the +ve all there then it should not show anything.

Regards,

Chandra.

«1

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Your dashboard prompt needs to populate a presentation variable based on the selection. If user select -ve then you need to have a CASe statement in your measure that checks the presentation variable and then only return measure values < 0 and the ELSE condition should return all values.

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Hi All,

    I have the following scenario of measure column data.

    pastedImage_0.png

    When i select ALL in the prompt it has to show all the data (4 rows) and when i select Negative then it has to show 1 row and 3 rd row of data.

    I have written the following filter at report level

    "-Analysis"."Balance - units" < case when '@{PV_Negative}{ALL}' ='ALL' then 99999999999 else 0 end

    The above formula displaying correct data for ALL condition

    for second condition   ("-Analysis"."Balance - units" < 0) it is displaying -ve values from 1st and 3rd rows.

    But i need to show +ve values also from 1st and 3rd rows.

    Regards,

    Chandra.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Try this: CASE WHEN '@{PV_Negative}{ALL}' = 'ALL' THEN "-Analysis"."Balance - units" WHEN '@{PV_Negative}{ALL}' <> 'ALL' THEN CASE WHEN "-Analysis"."Balance - units" < 0 THEN "-Analysis"."Balance - units" END END

    please note that I haven't tested this logic but something like this should do the trick...

  • Isn't this the same/strictly related to Report to display negative values only.  ?

  • Joel
    Joel Rank 8 - Analytics Strategist

    Well spotted @Gianni Ceresa! Duplicating questions doesn't double the chance of your issue being resolved, it has the reverse effect...

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Not the same questions, but both are asked by me only.

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Hi all,

    The above script is not displaying +ve values for the second condition. It's displaying -ve values from 1st and 3rd row.

    Regards,

    Chandra.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You're data doesn't support what you are trying to do ... you need a flag on the row that is there is any -ve in the row to mark it 'Y' then filter for that ...

  • User_JL3CW
    User_JL3CW Rank 6 - Analytics Lead

    Hi All,

    I am attaching screen shot with actual scenario.

    pastedImage_0.png

    In above report result the measure column "-units" displayed for monthly data.

    For the formula written:

    CASE WHEN '@{PV_Negative}{ALL}' = 'ALL' THEN "Analysis"."- units" WHEN '@{PV_Negative}{ALL}' <> 'ALL' THEN CASE WHEN "Analysis"."- units" < 0

    THEN "Analysis"."- units" END END

    The above formula is working fine for ALL condition from db prompt.

    It's displaying all the four rows as shown above.

    If i select "Negative" from DB prompt, It should display 3rd row and 4th row (all values -ve and +ve values)

    but it's displaying only -ve values from 3rd and 4th row.

    Regards,

    Chandra

  • Thomas gave you the solution: a flag in your data ...

    Because what you are asking is something like: if there is a single negative value for any period then display the whole row, with all the periods including positive and negatives ...

    I would say you can still make it, but the conditions will be a bit more weird.

    What else do you have in rows in your pivot? Because "- Units" doesn't repeat itself just for fun. So what you could try is a condition where "- Units" aggregated by that thing <> ABS("- Units") aggregated by that thing.

    PS: don't even try to just copy/paste these bits and hope it works, this gives you a direction, now follow it completing the missing bits and applying some elementary logic to make things valid in a "WHERE" condition of a query