Calculations work when using three separate columns, but not when adding all formulas in one column

[Pejme]

[Pay run site allowance payment] formula:

Show More

(
(
(
SUM(
SELECT(
Shift[Site allowance standard hours],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)+

SUM(
SELECT(
Shift[Site allowance RDO],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)
)/[RDO rate]
)+

SUM(
SELECT(
Shift[Site allowance overtime hours],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)
)

[Pay run site allowance payment] result in the app:

[Pay run hours] formula:

Show More

SUM(
SELECT(
Shift[Minutes worked],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)+

SUM(
SELECT(
Shift[Minutes leave],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)

[Pay run hours] result in the app:

Both results (30.11 and 14) are correct.

I want to know what [Pay run site allowance payment] /[Pay run hours] is (30.11/14=2.15). It works if I divide [Pay run site allowance payment] with [Pay run hours] in a separate column.

Show More

[Pay run site allowance payment]/[Pay run hours]

Both I use one single virtual column instead of the three columns above, it doesn't work. The result shows as 3.32 instead of the correct 2.15.

Show More

(
(
(
SUM(
SELECT(
Shift[Site allowance standard hours],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)+

SUM(
SELECT(
Shift[Site allowance RDO],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)
)/[RDO rate]
)+

SUM(
SELECT(
Shift[Site allowance overtime hours],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)
)/

SUM(
SELECT(
Shift[Minutes worked],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)+

SUM(
SELECT(
Shift[Minutes leave],
AND(
[_THISROW].[User]=[User],
[_THISROW].[Date]>[Date]
)
)
)

I feel like Phoebe trying to code Joey haha

https://www.youtube.com/watch?v=x3WNg6bIcHM&ab_channel=ReefsHaisham

[Aurelien · Accepted solution]

Hi @Pejme 

Did you forget a parenthesis here ?

[Pejme]

Thanks @Aurelien !

Turns out I am Joey ...

[MultiTech]

Just to add 2-cents here:

If you set your [User] column in the Shift table to a Reference to the User table - this will give you a reverse reference (on your User table) of all the [Related Shifts].

• This virtual column will essentially be the result of the following SELECT() formula:
  • Select(Shift[Shift_Record_ID], [User] = [_thisrow].[User])
    
    
• Which means... you DON'T have to do that query of your database again - they're all right there.
  • Granted... this every record from the user - but you get what I mean?

So if you wanted to get a subset of the records, you'd have to query the [Related Shifts] vc:

• Did you know... you can use a reverse reference inside a SELECT() statement?
• The result is an increase in efficiency, sometimes in orders of magnitude.
  • Select([Related Shifts][Shift_Record_ID], [Date] > [_thisrow].[Date])

Or some other version of a SELECT() formula. 

The key is the list dereference of the IDs (from your reverse reference), because select is looking for a list of table-record-IDs; and if you list dereference all the IDs of the records inside the [Related Shifts] virtual column... now you have a list of all the IDs (exactly what SELECT() is looking for) and everything works as expected.  You can just continue forth with further tightening the criteria.

[Pejme]

Love this! Thanks so much for helping me to get better by coming with suggestions for improvements. In my opinion,  this is gold. There are a lot of great people that have helped me answering my (most of the times obvious) questions. But it even better to get help with something you don't know enough about to understand you need help with. 

I think you were actually the one who also thought me the Select() function. I have been using it every time I need to find something in a table without understanding that there are better ways.