I cannot figure out why my value (from expression) is wrong

I have a table that gives/shows Accounts receivable (AR). In my app i show AR based on day ranges:

0 -30
31 - 60
61 -90
90+

I am trying to now get the total PER day range and show that.

I used this expression:
SUM(select(AR[90 + Days Virtual], [Customer Number] = [_THIS].[Customer Number]))

This is [90 + Days Virtual]:
IFS(
(TODAY() > ([Document Date] + 90)),
[Current Trx Amount]
)

Here is what the expression gives me in my app:

If you manually calculate this it is wrong. I am not sure why.

I must note I filter this slice taking out:

“Credit Memos” and “Payments”

AND( [Document Type] <> “Payments”, [Document Type] <> “Credit Memos”)

I looked at the raw data - and no where in “Payments” nor “Credit Memos” is there a value that would match the difference. Meaning I believe the slice filter is working correctly by taking out “Payments” and “Credit Memos”.

Is that pointing to the slice or the table?

That points to a VC i created in the table:

NOTE - i am only showing you 90 +. But i get the same issues with ALL columns.

Ok, AR is the table, not the slice.

As a test, you should see how many records just this part is pulling:
select(AR[90 + Days Virtual], [Customer Number] = [_THIS].[Customer Number])

How can i get/check that?

Create a new temporary virtual column, paste that into the editor, click the TEST button, screenshot those results, then delete the virtual column.

[_THISROW], not [_THIS].

1 Like

HA! Didn’t even notice that.

1 Like

I changed the VC to this, but i am still seeing the wrong sum:

SUM(select(AR[90 + Days Virtual], [Customer Number] = [_THISROW].[Customer Number]))

Alright, so now do the thing I suggested without the SUM part. Lets see what and how many values it is pulling.

I would but i am still confused on what you wanted me to try. Sorry. I need to create a virtual column in this table and click “Test”? where is that?

where is that - Expression assistant? I am not sure where that is in the editor.

nm

It seems to pull up 999 rows of the data. It is not ONLY selecting 90 + values…

I am not really sure it is pulling the correct data.

It is showing data from 61- 90 Days, is it suppose to show that? I am confused as to if this “Test” is just showing ALL rows - or if it is based on the expression.:

Ok, on this screen, on the left hand side, it should show you relevant results. So scroll to any row where the customer is Arena Offshore, and in the left side it should show a list of the individual values this expression is pulling.

Side note: this test screen will always show you all your columns in the table, but this can be useful in debugging your expressions on your own.

1 Like

Oh, ok. Thanks. Let me take a look…

Ok. That helped. It is NOT looking at my filter.

AND( [Document Type] <> “Payments”, [Document Type] <> “Credit Memos”)

I want ALL rows with those values - excluded. however- it still has them included?

So, that is why I asked my very first question. You are using a slice view the filters out Payments and Credit Memos from the table AR. But your expressions themselves operate on the ENTIRETY of the AR table. Slices don’t change the way expressions work on the underlying data.

SUM(
    SELECT(AR[90 + Days Virtual], 
        AND([Customer Number] = [_THIS].[Customer Number],
            [Document Type] <> “Payments”,
            [Document Type] <> “Credit Memos”
        )
    )
)
1 Like

OH! Dang. Ok. Thank you VERY much. I was LOST!!!

1 Like