To filter a fact based on an other fact — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

To filter a fact based on an other fact

Received Response
1
Views
3
Comments
3151906
3151906 Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

I have a report where I need to show:

1)  number of invoices paid before due date

2) amount related to invoices paid before due date

3) number of invoices paid after due date

4) amount related to invoices paid after due date

I have a drill action that shows per number of invoices what suppliers these are related to with the corresponding amounts..

these drill down reports are build separate for Due before and Due after with a general filter selecting only the invoices with due date >0.  That works fine but now I need to see the amounts as well on the summary page.

I tried to write a filter within the column

FILTER("Facts - AP Transactions"."AP Amount" USING ("Facts - AP Transactions"."No of Invoices Paid After Due" > 0))

but this gives an error:

Formula syntax is invalid.
Aggregate is not allowed within USING clause of a FILTER operator. (HY000)
SQL Issued: SELECT FILTER("Facts - AP Transactions"."AP Amount" USING ("Facts - AP Transactions"."No of Invoices Paid After Due" > 0)) FROM "Financials - AP Transactions"

Then I tried to write a case for, but this gives me the total amount (so for both paid before as after due date)

CASE WHEN "Facts - AP Transactions"."AP Times Paid Before Due"> 0 THEN "Facts - AP Transactions"."AP Amount"ELSE 0 END

Any Idea how I can realize this?

Thanks

Answers

  • Carina Mendes
    Carina Mendes Rank 3 - Community Apprentice

    Hi,
    May work if you change the date value by filter parameter.

    See the samples:

    1) COUNT(FILTER("you colum with value" USING ("your colum due date" < date '2016-10-11')))

    2) SUM(FILTER("you colum with value" USING ("your colum due date" < date '2016-10-11')))

    3) COUNT(FILTER("you colum with value" USING ("your colum due date" > date '2016-10-11')))

    4) SUM(FILTER("you colum with value" USING ("your colum due date" > date '2016-10-11')))

    If in case your date is dynamic, you can even use a presentation variable.

    I hope this response is help.

    Regards,
    Carina Mendes.

  • 3151906
    3151906 Rank 1 - Community Starter

    Hello Carina,

    Thanks for your help. Unfortunately the problem is not the date.

    the nbr of Invoices paid before and after due date are facts that show the number of invoices meeting the criteria.

    I just need to be able to filter an other fact ( AP Amount) twice in one report.

    I tried you way but then I get still the error Aggregate is not allowed within USING clause of a FILTER operator.

    But thanks anyhow.

    Best regards,

    Margie

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    Hi,

    You got error because you're trying to use a AGG column on DataFilter.

    FILTER("Facts - AP Transactions"."AP Amount" USING ("Facts - AP Transactions"."No of Invoices Paid After Due" > 0))


    Is above column ("MARKED") set to aggregate on RPD?


    If you have invoice by invoce on your fact table, could you use a detail column like a flag to filter invoices paid after/before due instead of agg column?


    Felipe Idalgo