I have two tables. One is a table of invoice. Thereโs a [PAY] and [PAY TYPE]. I have another table that will sum up for the day. [Pay] is an amount, [Pay Type]= โcreditโ. I want to create a virtual row that sums up all the credits for the day.
select all with [pay type] = โcreditโ
sum up [Pay] if [Pay Type] = โcreditโ and [date] = today
Can somebody help with the expression?
My guess: sum(select([PAY TYPE] = CREDIT, [PAY], TRUE))
Well, you got the SUM(SELECT()) portion correct, but that SELECT expression is nowhere near properly formed, give this SELECT article another review:
Youโll also need this:
Hereโs my actual expression.
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = โCreditโ, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))
I want to filter todayโs report( Daily Report[App Date]) with my Datasheet(Form Responses 1[App Date])
It doesnโt work.
For the criteria part, Iโm trying to pull only data from Table 1 that has the same [date] from Table 2. I tried
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],AND([PAY TYPE] = โCreditโ, Daily Report[APP DATE] = Form Responses 1[App Date], TRUE)))
Daily Report[APP DATE] = Form Responses 1[App Date] โ Iโm trying to filter the date from one table with another. Itโs valid but not summing up. Is my first expression wrong?
Ahhโฆ I got it to work with this.
SUM(SELECT(FORM RESPONSES 1[PAT PAYMENT],and([PAY TYPE] = โCreditโ,[app date] = today()), FALSE))
However, what can I use instead of [app date] = today() ? What if I want to choose another day besides today?
I assume youโre running this expression from the context of a record in your Daily Report Table? Try replacing TODAY()
with [_THISROW].[APP DATE]
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |