Re: How can I reduce the formula?

ME_Webdesign · 01-20-2022 11:24 PM

Hello, I want to take data from a user, it has to be the last data.
I created this, and it works, but I don’t want to do something so complex because I need to use it many times:

IF([_THISROW].[MES DEVENGADO]<12,

SUM(SELECT(LIQUIDAR_UNIDAD[ADEUDA FO],
AND(
[ID_UNIDAD]=[_THISROW].[ID_UNIDAD],
[AÑO DEVENGADO]=[_THISROW].[AÑO DEVENGADO],
[MES DEVENGADO]=[_THISROW].[MES DEVENGADO]-1
)
)),

SUM(SELECT(LIQUIDAR_UNIDAD[ADEUDA FO],
AND(
[ID_UNIDAD]=[_THISROW].[ID_UNIDAD],
[AÑO DEVENGADO]=[_THISROW].[AÑO DEVENGADO]-1,
[MES DEVENGADO]=[_THISROW].[MES DEVENGADO]
)
))

)

jyothis_m

Have you tried [_ThisRow].[_RowNumber]-1

can you please explain your requirement a little bit more in words because with the lengthy formula I couldn’t figure out your exact requirement.

graham_howe

There is probably a better way of writing it depending on the exact requirements, but if you just want to reduce the size of the expression then the following should do the same:

SUM(
  SELECT(
    LIQUIDAR_UNIDAD[ADEUDA FO],
    AND(
      [ID_UNIDAD]=[_THISROW].[ID_UNIDAD],
      [AÑO DEVENGADO]=[_THISROW].[AÑO DEVENGADO]
        - IF([_THISROW].[MES DEVENGADO]<12,0,1),
      [MES DEVENGADO]=[_THISROW].[MES DEVENGADO]
        - IF([_THISROW].[MES DEVENGADO]<12,1,0)
    )
  )
)

graham_howe

Thinking about this a little more, I assume MES stands for month and therefore can only have a value of 1 to 12. If that is the case then we should be able to use some math functions to completely remove the conditional operation.

SUM(
  SELECT(
    LIQUIDAR_UNIDAD[ADEUDA FO],
    AND(
      [ID_UNIDAD]=[_THISROW].[ID_UNIDAD],
      [AÑO DEVENGADO]=[_THISROW].[AÑO DEVENGADO]
        - FLOOR([_THISROW].[MES DEVENGADO] / 12),
      [MES DEVENGADO]=[_THISROW].[MES DEVENGADO]
        - (1 - FLOOR([_THISROW].[MES DEVENGADO] / 12))
    )
  )
)

ME_Webdesign

Thanks, I am going to test it.

Steve

My take:

SUM(
  SELECT(
    LIQUIDAR_UNIDAD[ADEUDA FO],
    AND(
      ([ID_UNIDAD] = [_THISROW].[ID_UNIDAD]),
      IF(
        ([_THISROW].[MES DEVENGADO] < 12),
        AND(
          ([AÑO DEVENGADO] = [_THISROW].[AÑO DEVENGADO]),
          ([MES DEVENGADO] = ([_THISROW].[MES DEVENGADO] - 1))
        ),
        AND(
          ([AÑO DEVENGADO] = ([_THISROW].[AÑO DEVENGADO] - 1)),
          ([MES DEVENGADO]=[_THISROW].[MES DEVENGADO])
        )
      )
    )
  )
)

Please explain.

ME_Webdesign

Hello, thank you all for your answers.
I am passionate about Excel and I loved this AppSheet initiative.

I made an application that manages the common expenses of buildings in horizontal property. In this cell I want it to take the data of the previous month and add it, in case we are in December it will take the last date of the previous year.

I’m half dyslexic, so I just realized that instead of 12 it should have been month 1 haha, so I’m not going to use the answers but I’m still working with your input thanks.

It has been quite complex and I think that is why there are sheets to which I can not filter, for this reason, I think that reducing the code will be a solution.

Best!!