
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum of Variances by Item
I am currently attempting to review a set of data related to variances in inventory counts. A very simple example of the data is as follows;
Count # Item Variance
1 ABCD -3
2 ABCD 2
2 ABCD -4
2 EFGH 3
Here is the problem I am having. I have created a table and formulas to sum the variances by Count # as well as the absolute value of those variances. What I would like to present is the absolute variance of the variance by item. As of now, the only way I have come up with to complete this is to use the source data and create a table with the count and item as dimensions and sum the relevant fields, then export the file and load it into a new app and re-perform the analysis. For reference, the new file based on my example data above looks like this:
Count # Item Variance Absolute Variance
1 ABCD -3 3
2 ABCD -2 2
2 EFGH 3 3
My question is can I calculate the absolute value of the variance amounts by using the combination of count # and item # in a formula without . I'll likely need to use the "FABS(SUM(" with some type of expression, just need someone to help me with it if possible. Thanks in advance for any help you can give.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can prepare this data in the backend itself in the same app
SourceData:
Load Count#, Item, Variance from Source_file;
Transform:
Load Count#, Item, Sum(Variance) as Variance, Fabs(Sum(Variance)) as Absolute_Variance
Resident SourceData
Group by Count#, Item;
Drop Table SourceData;
or in the front end try in the combination of Aggr, sum and fabs
Example:
Sum(Aggr(Fabs(Sum(Variance)), Count#, Item))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can prepare this data in the backend itself in the same app
SourceData:
Load Count#, Item, Variance from Source_file;
Transform:
Load Count#, Item, Sum(Variance) as Variance, Fabs(Sum(Variance)) as Absolute_Variance
Resident SourceData
Group by Count#, Item;
Drop Table SourceData;
or in the front end try in the combination of Aggr, sum and fabs
Example:
Sum(Aggr(Fabs(Sum(Variance)), Count#, Item))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Celambarasan, your "front end" solution worked perfectly and allowed me to keep the full analysis over the original data while adding analysis around the consolidated numbers. I truly appreciate your help and thank you for your time.
