Hi everybody! I am trying to add a column to a collection using a Filtering function according to the current row value, but I don´t find a way to use that Filtering function with a dynamic condition instead of a static one.
For instance, my collection looks like this:
I want the column "DOCUMENTOS_ÚNICOS_ACUMULADOS" (3rd column) to contain the cumulative frequency of the column "DOCUMENTOS_ÚNICOS" (2nd column) for every value in the "MES" column (4rd column). That means, that "DOCUMENTOS_ÚNICOS_ACUMULADOS" for "MES" = "Septiembre" is going to be the sum of all the "DOCUMENTOS_ÚNICOS" where the "MES_NÚMERO" is less or equal to 9. Then, "DOCUMENTOS_ÚNICOS_ACUMULADOS" for "MES" = "Octubre" is going to be the sum of all the "DOCUMENTOS_ÚNICOS" where the "MES_NÚMERO" is less or equal to 10 and so on. In that sense, "DOCUMENTOS_ÚNICOS_ACUMULADOS" for "MES" = "Septiembre" should be 39 and "DOCUMENTOS_ÚNICOS_ACUMULADOS" for "MES" = "Octubre" should be 72 (39 + 33).
My problem is that I don´t know how to refer the value of a particular column in the current row. I was thinking of something like this:
ClearCollect(Collection_2, AddColumns(Collection_1, "DOCUMENTOS_ÚNICOS_ACUMULADOS", Sum(Filter(Collection_1, MES_NÚMERO <= MES_NÚMERO value for the current row so that this number would be 9 for the first row and 10 for the second one), DOCUMENTOS_ÚNICOS)))
But that didn´t work.
Thanks in advance for any helpful comment.
