To filter a fact based on an other fact

Question

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

Felipe_Idalgo · Answer

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

3151906 · Answer

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

Carina Mendes · Answer

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.