measureqliksense

Calculating a measure on all rows based on a subset of the data

I have a dataset containing data like following:

Customer invoice number invoice date payment date payment_days open_flag
Customer 1 invoice 1 1/1/24 10/1/24 9 no
Customer 1 invoice 2 1/1/24 20/1/24 19 no
Customer 1 invoice 3 5/1/24 10/1/24 5 no
Customer 2 invoice 4 11/1/24 13/1/24 2 no
Customer 2 invoice 5 11/1/24 13/1/24 2 no
Customer 3 invoice 6 12/1/24 18/1/24 6 no
Customer 1 invoice 7 1/2/24 NA yes
Customer 2 invoice 8 3/2/24 NA yes
Customer 3 invoice 9 4/2/24 NA yes

and I would like to calculate the following columns - which shouldn't be affected by any filter selections!

... | **calculated customer avg** | **expected payment date** |
... | ----------------------- | ------------------------- |      
... | (9 + 19 + 5) / 3 = 11   |          12/1/24          |
... | (9 + 19 + 5) / 3 = 11   |          12/1/24          |
... | (9 + 19 + 5) / 3 = 11   |          16/1/24          |
... |    (2 + 2) / 2 = 2      |          13/1/24          |
... |    (2 + 2) / 2 = 2      |          13/1/24          |
... |      (6) / 1 = 6        |          18/1/24          |
... | (9 + 19 + 5) / 3 = 11   |          12/2/24          |
... |    (2 + 2) / 2 = 2      |          5/2/24           |
... |      (6) / 1 = 6        |          10/2/24          |

My problem is that I can only calculate the avg. days on the closed invoices, but I can't use these calculations on the open invoices.

I can only work in the sheets (Do not have access to the data load editor).

I would like to use this look at the expected payment dates for open invoices, meaning that the calculation should still return a value if I filter out all closed invoices.

I am only able to calculate the avg (where I have removed outliers). But these are only returned on closed rows...

= Avg(
        IF(
            [invoice date] <> [payment date] and SQRT(POW([invoice date] - [payment date],2)) <= 200, 
        [invoice date] - [payment date], 
        null()))

Thank You

Solution

  • If I get your question right, this function should do what you want:

    Date(invoice_date + Aggr({1} NoDistinct Avg(payment_date-invoice_date), Customer), 'D/M/YY')

    It will return the expected_payment_date, even when you filter out the closed invoices, like:

    enter image description here

    The Aggr() aggregates the payment_days independently from the table object and the set identifier {1} ignores all selections.

    Data load statement:

    Data:
Load Customer,
     [Invoice Number],
     Date#([Invoice Date], 'D/M/YY') as invoice_date,
     Date#([Payment Date], 'D/M/YY') as payment_date,
     payment_days,
     open_flag     
Inline [
Customer,   Invoice Number, Invoice Date,   Payment Date,   payment_days,   open_flag
Customer 1, invoice 1,  1/1/24,     10/1/24,    9,  no
Customer 1, invoice 2,  1/1/24,     20/1/24,    19, no
Customer 1, invoice 3,  5/1/24,     10/1/24,    5,  no
Customer 2, invoice 4,  11/1/24,    13/1/24,    2,  no
Customer 2, invoice 5,  11/1/24,    13/1/24,    2,  no
Customer 3, invoice 6,  12/1/24,    18/1/24,    6,  no
Customer 1, invoice 7,  1/2/24,     ,            ,  yes
Customer 2, invoice 8,  3/2/24,     ,            ,  yes
Customer 3, invoice 9,  4/2/24,     ,            ,  yes
];