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! Go to 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โ
)
)
)
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
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
User | Count |
---|---|
59 | |
25 | |
13 | |
12 | |
6 |