SUM of Filter Data

[ABBA]

Hi
I was helped by @Steve and he helped me write the expression to Filter data by Start date and End Date. I now have the data filtered as below
IN( [Date], SELECT(Meter [Date], AND( [Date]>=ANY(DateFilter[Start Date]), [Date] <=ANY(DateFilter[End Date]))))
Now I have Meter data (more than one meter) and there are 3 columns Meter[Oil], Meter[Water] and Meter[Gas] that I want to Aggregate or SUM for the chosen dates and only want Unique reading (1 reading) per Meter
How do I achieve this?

Thanks
Amol

[Kirk_Masden]

Hi @ABBA!

Do you understand the expression that @Steve helped you with? Here’s how I understand it:

First, in the middle, you have an expression that gives you a list of all of the meter dates between the “Start Date” and the “End Date”:

SELECT(Meter [Date], AND( 
	[Date]>=ANY(DateFilter[Start Date]), 
	[Date] <=ANY(DateFilter[End Date])))

For more information see

help.appsheet.com

SELECT()

Gather column values from row in table or slice

You mentioned that you don’t want duplicates. Here’s an example from the SELECT() article:

SELECT(Students[First Name], ([Class of] = "2020"), TRUE) : A list of distinct first names (duplicates omitted) of the students of the class of 2020.

Then, the expression checks to see if the date of this record is in that list:

	IN( [Date], 
		SELECT(Meter [Date], AND( 
			[Date]>=ANY(DateFilter[Start Date]), 
			[Date] <=ANY(DateFilter[End Date])))
		)

See

help.appsheet.com

IN()

Is item in list?

Now what you want to do is sum lists. There first step is the same; make a list expression to find everything you want to sum up. Then, you want to put that inside a sum() expression. Look at

help.appsheet.com

SUM()

Arithmetic sum of list items

By the way, it seems that omitting duplicates is the default.

To make a long story short, I think that all you will need to do is replace the IN() expression with a SUM() expression and change the names of columns as needed.

Is this enough to help?

[ABBA]

I tied what you mentioned. But gave me an error in expression.

I want to sum the Filtered Data. Currently, I get the Data Filtered between Start Date and End Date. Please see attached

This gives me Data by Meter
I want to have just 1 row by MeterName and SUM[Oil,Bbls], SUM[WATER,Bbls], SUM[Emulsion,Bbls]

[Suvrutt_Gurjar]

Hi @ABBA,

If I have correctly understood your requirement, you may wish to try the following approach. Please note that the approach uses virtual columns to compute various aggregations you wish to have. Also due to inherent nature of aggregation functions that span over many rows, these VCs may have adverse impact on your app sync times.

1. You may have 3 VCs ,one each for SUM[Oil,Bbls], SUM[WATER,Bbls], SUM[Emulsion,Bbls] with an expression something like

SUM(SELECT(Meter [Oil-Bbls], AND( [Date]>=ANY(DateFilter[Start Date]), [Date] <=ANY(DateFilter[End Date], [Meter Name]=[_THISROW].[Meter Name])))))

The above expressions will sum the [Oil-Bbls] for the selected date range and for each meter.

2. Since you wish to have only a single row to show aggregation for each meter, you may have another slice on the Meter table. The slice is say titled " Sums By Meter"

The filter expression for this slice may be something like
IN([Date], LIST(MAX(SELECT(Meter [Date], AND( [Date]>=ANY(DateFilter[Start Date]), [Date] <=ANY(DateFilter[End Date], [Meter Name]=[_THISROW].[Meter Name]) ))))
The slice expression will filter the row with the maximum date for each meter and the VCs described in 1) above in that row will show the respective aggregated values.

3. You may have a summary view (table or deck) called say " Sums By Meter" based on the slice described in 2) above

[Amanagarwal]

Suvrutt_Gurjar:

SUM(SELECT(Meter [Oil-Bbls], AND( [Date]>=ANY(DateFilter[Start Date]), [Date] <=ANY(DateFilter[End Date], [Meter Name]=[_THISROW].[Meter Name])))))

SUM(SELECT(Meter [Oil-Bbls], AND( [Date]>=ANY(DateFilter[Start Date]), [Date] <=ANY(DateFilter[End Date], [Meter Name]=[_THISROW].[Meter Name])))))
HELLO, @Suvrutt_Gurjar I TRIED USING THIS FORMULA IN VC’S BUT IT SHOWS ANY FUNCTION USED INCORRECTLY , CAN YOU PLEASE CHECK WHAT IS WRONG WITH THIS FORMULA

[ABBA]

Thank you so much @Suvrutt_Gurjar Sir
I appreciate your help.
I can create a new sheet in Google Sheet and have columns that shows the SUM numbers

Is it possible?

Thanks
Amol

[Suvrutt_Gurjar]

hI @ABBA,

I believe in general it should be possible. You may aggregate using the SUMIF() Google sheet formulas in the same Meter table or a different table.

However please note that as per my understanding the aggregated values from spreadsheet formula will reflect in respective individual records in the app only after the record is opened and closed.

So my suggestion will be to start with small test data and one aggregation field and test it out whether it meets your requirement.