Expression to return aggregation of a column value...

Ankit_Android · 10-10-2019 03:35 AM

Hi,

I have a google worksheet called Transactions… which has 3 columns:

User, Type, Value

I would like to write an expression to return summation of column “Value” for each unique value in column “Type”

For example… if my table has 10 entries

User Type Value

Alpha Type A 100
Alpha Type B 200
Alpha Type C 150
Beta Type A 100
Beta Type B 200
Beta Type C 150
Charlie Type A 100
Charlie Type B 100

Then my query should result in
Type Value
Type A 300
Type B 500
Type C 300

I hope someone can help me with this.

Suvrutt_Gurjar

Hi @Ankit_Android,

You have mentioned another table in your subject line. I believe you may wish to give somewhat more details like below

Is it correct to assume , the example of the table you have given with 8 records is one table, called say UserType table and the table where the values for the type are aggregated with three rows is another table , called say AddValues

Is there any referencing between these two tables? For example is AddValues table referenced in UserType Table?

In general presuming the tables are not referenced to each other , the expression for aggregate value in of Value column ( a virtual column) in AddValues table could be something like
SUM(SELECT(UserType[Value],[TypeU]=[_THISROW].[TypeS]))

Here TypeU is type column name in user table and TypeS is type column name in AddValues table.

You may wish to update if the tables are referenced and if you would like value to be updated in a real column in AddValues table. Then there could be a different approach for aggregation.

Ankit_Android

@Suvrutt_Gurjar,

You are absolutely right. Let me explain it again.

Table 1: UserEntry has 5 columns:

Date, Year, User, Type, Value

Table 2: YearlySummary has 1 Column

Year

Now, I want to add a virtual column in Table 2 which will be of type “List” which should show unique list of entries from Table 1 for column “Type” and cumulative value of column “Value” for given Year and Type

I will create a view for Table 2:

which will show all the unique values of Year and a aggregated values of all the types for that Year.

Hope it helps.

Suvrutt_Gurjar

Hi @Ankit_Android,

Thank you for more details. The table 2 does not seem to have a type column. Could you please mention how to you plan to have a yearly summary by type in the table 2? Will there be 3 summaries for each year by Type A ,B and C? In that case do you wish to have 3 VCs per record ( that is per year) in table 2?

Also could you please mention you wish to have list of values and not an aggregated value right?

Ankit_Android

Hi @Suvrutt_Gurjar,

Table 2 doesn’t have Type column and hence the idea is to bring unique list of values from column “Type” from Table 1 along with consolidated/aggregated values for each Type for that year in table 2 in VC column which will be of list type.

For e.g.
Table 1:
Date Year User Type Value

01-Sep-2018 2018 Alpha Type A 100
05-Sep-2018 2018 Alpha Type B 200
07-Sep-2018 2018 Alpha Type C 300
01-Sep-2018 2018 Beta Type A 100
05-Sep-2018 2018 Beta Type B 200
07-Sep-2018 2018 Beta Type C 300
01-Sep-2018 2018 Charlie Type A 100
05-Sep-2018 2018 Charlie Type B 200
07-Sep-2018 2018 Charlie Type C 300

Table 2:

Year
2018

I want to write an expression for a VC column in Table 2: which will give following results:

Type Value
Type A 300
Type B 600
Type C 900

Please note: the example given is a simpler form than the actual data sets which has multiple columns and rows. and hence I would be creating multiple VCs in table 2 which will fetch similar list for different columns for a given year. for e.g

User Values
Alpha 600
Beta 600
Charlie 600

I hope this provides a better view

Steve

If all you want are virtual columns in Table 2, just create the virtual columns with suitable app formulas, e.g.:

SUM(
  SELECT(
    Table 1[Value],
    AND(
      ("Type A" = [Type]),
      ([_THISROW].[Year] = [Year])
    )
  )
)

Ankit_Android

I believe your formula will not result in unique values. Let me check

Ankit_Android

In addition, your formula is incorrect for my requirement. I want to fetch all the unique values of column “Type” and get corresponding aggregated values of column “Value” in Virtual column

Steve

My expression does that, if you’re using the term “virtual column” correctly.

Alan_Martins

Hi team, I have the some problem:

1 - I have a table (Spreadsheet) with all projects

2 - I want to create a table view bellow, with graphics without duplication (consolidate)

How can i do that?

OptimiX_XcrY

What I am interested in is if I can build in a single VC , a list of unique values and their aggregates (no second table with unique values as ref, no multiple VC because the unique values varies too often).

I will mainly use this solution (if it exists) , to show a resume of different children quantities,before final saving, for visual fast check . Currently I only have a count of children quantities ,all types together.

Instead of a simple “2” in the screenshot, I will have :

“P6 A 1, P3 40 1 , etc”

Steve

There’s no trivial way to do this. You could attack it by adding a virtual column to the child table, then generating the summary column in the parent from those child columns, but this would all involve a lot of VC computations, which could be prohibitive for large data sets.

OptimiX_XcrY

Thank you, I will try. My app is for add only (false security filter, no data loading, so no computation issues)

OptimiX_XcrY

I did it a bit childish, but it works, by only adding VC to parent table. I took in consideration that I will have a maximum of 5 unique children model (but it can be extended).

VC [modeley] is just a helper VC.

It is not fine tunned, but here it is for other rookies like me :

VC[xyz] = List (Concatenate(index([_thisrow].[modeley];1) ; " = “; count(select(detalii stingatoare tof[model],and([idx]=[_thisrow].[idx];[model]=index([_thisrow].[modeley];1));false));”
") ;
Concatenate(index([_thisrow].[modeley];2) ; " = “; count(select(detalii stingatoare tof[model],and([idx]=[_thisrow].[idx];[model]=index([_thisrow].[modeley];2));false));”
") ;

Concatenate(index([_thisrow].[modeley];3) ; " = “; count(select(detalii stingatoare tof[model],and([idx]=[_thisrow].[idx];[model]=index([_thisrow].[modeley];3));false));”
") ;

Concatenate(index([_thisrow].[modeley];4) ; " = “; count(select(detalii stingatoare tof[model],and([idx]=[_thisrow].[idx];[model]=index([_thisrow].[modeley];4));false));”
") ;

Concatenate(index([_thisrow].[modeley];5) ; " = “; count(select(detalii stingatoare tof[model],and([idx]=[_thisrow].[idx];[model]=index([_thisrow].[modeley];5));false));”
")
)

VC[modeleY] = select(detalii stingatoare tof[model],[idx]=[_thisrow].[idx],true)

VC[modelex] = select(detalii stingatoare tof[model],[idx]=[_thisrow].[idx],false)

Expression to return aggregation of a column value (e.g "Value") from another table for unique list of another column