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

Tiger1
Participant V

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โ€.

Solved Solved
0 26 370
1 ACCEPTED SOLUTION

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โ€
        )
    )
)

View solution in original post

26 REPLIES 26

Bahbus
Participant V

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].

HA! Didnโ€™t even notice that.

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

Tiger1
Participant V

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.

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โ€
        )
    )
)

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

Hey can you help me correct these as well? I was also trying to use a filter in a slice to NOT show Payments and credit memos. These must be wrong as wellโ€ฆ

IFS(
(TODAY() <= ([Document Date] + 30)),
[Current Trx Amount]
)

Well, are they wrong? I donโ€™t know what it is supposed to be.

To be honestโ€ฆ i never checked that data. No one here has told me it is wrong. However i did the same for those. I ran that expression in the tableโ€ฆ then built a slice to NOT show โ€œPaymentsโ€ and โ€œCredit memosโ€.

Which i now know is wrongโ€ฆ

Yes, but this expression isnโ€™t doing any kind of SUM or SELECT or anything that seems like it would affected by Payments or Credit Memos.

Oh. Ok. So if it is simply hiding that row, using the slice is ok to use?

Correct.

Ok. Thanks again

Top Labels in this Space