Group aggregate Display a numeric summary of the rows in each group

[aminsaleh]

Hi, in the Group aggregate Display a numeric summary of the rows in each group.

Can I have multiple aggregates? for example in each row I have 3 method of payment:

1- cash

2- card

3- epayment

can I have the aggregate shown for each method, for example

cash $100

card: $95

epayment: $120

>> Or there other method to show the sum in front of the user?

Thanks 

[TeeSee1]

I can't test this but try creating a virtual column with an expression which concatenates the date and the SUM values of each method belonging to the date and group by this column.

[aminsaleh]

@TeeSee1  Thanks indeed

Would you plz write it for me the virtual column with an expression which concatenates the date and the SUM values of each method belonging to the date.

I could not find exact resources for what you have suggested. 

[TeeSee1 · Accepted solution]

[date col] + "cod: " + sum(select(table[cod], [date col]=[_thisrow].[date col])) + .....

[aminsaleh]

@TeeSee1 

Hi, it gives an error

The expression is valid but its result type 'Date' is not one of the expected types: Price

I have also corrected it but it does not calculate values as I want.

plz have a look at this image below:

Table is cour

Date: contains date

Cod, epayment and card all contain prices

>>amount is the new virtual column 

Result in green should be like this (for example on first date)

Note >> View group by date and  sum virtual column amount

>> result like this: cod: $30 - Epayment: $15 - Card: $12

[TeeSee1]

The type should be text. The result is a mixture of date number and text so it has to be of type text

[aminsaleh]

@TeeSee1 

CONCATENATE("cod", sum(select(cour[ COD], [Date]=[_thisrow].[Date])))

It does not work for me.  above formula gives date plus zero.

also on each row it says cod40 (40 is the total of all cod) 

----------

what I need to show on top of each date group is like this:

7/23/2024: cod 40 - Epayment 15 - card 12

please write your answer in detail if you do not mind. Thanks.

[TeeSee1]

You are not writing an expression like I suggested in my first post. 

Concatenate the date column as well as three sum( select ()) expressions representing each payment method. And you should do group by this virtual column, which is the column amount in your case.

[leefarrell]

Yes, you can definitely display multiple aggregates in Google AppSheet. For your example, you can create virtual columns to sum each method of payment (cash, card, epayment) individually.

Here's a quick way to do it:

Create Virtual Columns:

For cash, create a virtual column with the formula: SUM(SELECT(Table[Cash], [GroupColumn] = [_THISROW].[GroupColumn]))
For card, use: SUM(SELECT(Table[Card], [GroupColumn] = [_THISROW].[GroupColumn]))
For epayment, use: SUM(SELECT(Table[Epayment], [GroupColumn] = [_THISROW].[GroupColumn]))
Display the Aggregates:

Add these virtual columns to your view to show the aggregates for each method of payment.
This way, you'll be able to see the sum of cash, card, and epayment for each group directly in the app.

If you want to present this in a more user-friendly format, you could use a Dashboard or a custom summary view that combines these virtual columns.

Hope this helps!

[aminsaleh]

Hi @leefarrell  

I do not see something new. see please the red columns are the original and the green are the virtual. Nothing new here. It is just a repetition. 

[aminsaleh]

Thanks all now is solved.

concatenate([_THISROW].[Date]," cod:$",SUM(Select(cour[COD], [_THISROW].[Date] = [Date]))," epayment:$",SUM(Select(cour[Epayment], [_THISROW].[Date] = [Date]))," card:$",SUM(Select(cour[Card], [_THISROW].[Date] = [Date])))