Dynamic Sum based on other columns values

Ask for Help
1

Hi there,

I’m trying to get the rollup of a colum…but this rollup needs to changed based on criteria.
It is kind of a Rollup combined with a SUMIF but I’m failing to do it so far…
In excel, I would have use MATCH and INDEX to get there - any idea what would be the best way ?

Hopefully the below screen shot is self-explanatory : I want in column “PoidsCamion” to sum value of colum “Poids Net Big Bag” until the “Numéro Camion” changes (or until “Numéro Big bag” is back to 1 which is the same).

Any guidance would be much appreciated !!

(note : on the picture, I manually populated value in column “PoidsCamion” :wink: )

image
image591×619 62.6 KB

2
  • Create a Query column that targets the table, and use the following filter:
    – Numero Camion equals This row → Numero Camion
  • Do your rollup->sum via that Query column
3

Wow ! Brillant !
I would have never though about this :slight_smile:

Thanks a lot, exactly what I was after !

4

The Query column is incredibly powerful.
If you’d asked the same question a year ago, the solution most likely would have involved a dozen or more computed columns :slight_smile:

5

Good to know, I’ll look deeper in the documentation for this Query column then :wink:

6

This is great, I’m working on a video about QueryVSRelations - may include a similar example.

7

Well, isn’t that a happy coincidence :rofl:

8

If you are looking for examples, a very good argument for the Query column is the ability to conditionally check a range of dates. This was something that was next to impossible to do with a Relation. Imagine trying to find matching child records that have a date that falls in-between a start and end date on the parent record.

9

@Darren_Murphy :stuck_out_tongue_winking_eye:

@Jeff_Hager Nice! That’s awesome. Working on Query Column video today so will try to build that in.

Did the QueryVsRelation one yesterday. https://www.youtube.com/watch?v=wa1OEGBG-3s

tutorial