Jump to content
• 0

# Calculation From Aggregation

## Question

I'm a little uncertain how this can be done. What I have essentially, is a lookup table that is combined in a view with a large spreadsheet. The lookup table indicates a target value that is matched up in the view to the spreadsheet based upon the initials of a person. The view is then used in a data page. However, in the data page I'd like to calculate the difference between the target (extracted from the lookup table to the view) with an aggregation that uses the SUM for the individual initials having records in the spreadsheet. It appears the data page doesn't allow the aggregated fields to be compared. My question then becomes how to perform a calculation that determines the difference between an aggregation computed from calculated fields in a data page, with a static target number that is merged from a lookup table into a view. I can show the two values side by side in a visualization but cannot figure out how to make a new field representing the difference between the target and the aggregation (the aggregation representing tha actual productivity of the individual).

UPDATED:

To clarify, this could be achieved if the view that merged the lookup table with the overall table only inserted the quantity once for the group of records matching by individual initials, and left the rest values 0. However, that workaround doesn't seem to be possible. Perhaps there's some intermediary step that I'm missing or some other programmatic way to make a calculation between two different aggregations.

## Recommended Posts

• 0

You need to write a custom SQL select statement to get the values and compare them in an aggregation for instance:

SELECT SUM (fieldname) FROM tablename WHERE ...

##### Share on other sites
• 0
On 1/16/2018 at 5:44 PM, MayMusic said:

You need to write a custom SQL select statement to get the values and compare them in an aggregation for instance:

SELECT SUM (fieldname) FROM tablename WHERE ...

Do you know how to select second highest or lowest number in a table?

##### Share on other sites
• 0

For instance if you want to pull the second lowest Id for last name Kevin you would have:

```SELECT TOP 1 Id FROM (

SELECT TOP 2 Id FROM
AllData WHERE Last_Name = 'Kevin' ORDER BY Id ASC) AS tt ORDER BY Id DESC```

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×

• #### Activity

• Leaderboard
×
• Create New...