topic Re: Sum with AddYears in condition in App Development

Sum with AddYears in condition

Paulonst — Thu, 01 Apr 2021 13:46:08 GMT

Hi everyone,

I want to sum same period of previous year according to seleccion.

When I put this in expression it works for the seleccion (I know it's not needed, it was addesd just to check code)

sum({< [Data] = {">=$(=min([Data])) <=$(=max([Data]))"} >} QtdCx)

But when I try the same with AddYears() it returns 0!

sum({< [Data] = {">=$(=AddYears(min([Data]),-1)) <=$(=AddYears(max([Data]),-1))"} >} QtdCx)

Can anyone help?

Re: Sum with AddYears in condition

dplr-rn — Thu, 16 Jan 2020 14:26:51 GMT

that is odd.

try formatting about the addyears function to your date format

Re: Sum with AddYears in condition

Paulonst — Thu, 16 Jan 2020 15:37:04 GMT

On my script this is the date format
SET DateFormat='DD/MM/YYYY';

Can you please explain better your suggestion?

Re: Sum with AddYears in condition

dplr-rn — Thu, 16 Jan 2020 15:39:37 GMT

try this

$(=Date(AddYears(min([Data]),-1),'YOURFORMAT'))

Re: Sum with AddYears in condition

Paulonst — Thu, 16 Jan 2020 15:46:53 GMT

Tried like this and same result: 0!

sum({< [Data] = {">=$(=Date(AddYears(min([Data]),-1),'DD/MM/YYYY')) <=$(=date(AddYears(max([Data]),-1),'DD/MM/YYYY'))"} >} QtdCx)

To try to help, here's the return of this code:
min([Data]) : 01/01/2020
max([Data]):16/01/2020
AddYears(min([Data]),-1):01/01/2019
AddYears(max([Data]),-1): 16/01/2019

Re: Sum with AddYears in condition

dplr-rn — Thu, 16 Jan 2020 15:47:55 GMT

as i said the original one (without date formatting) looks correct and works for me on my own example.

do you see a preview of the output like below?

gut feeling is its something to do with date format

Re: Sum with AddYears in condition

Kushal_Chawda — Thu, 16 Jan 2020 15:52:01 GMT

I think your expression is fine. Only you need to exclude the other time filters to work it properly

sum({<Year,Month,[Data], [Data] = {">=$(=AddYears(min([Data]),-1)) <=$(=AddYears(max([Data]),-1))"} >} QtdCx)

Re: Sum with AddYears in condition

Paulonst — Thu, 16 Jan 2020 16:37:19 GMT

Thanks @Kushal_Chawda for the solution and @dplr-rn for the help!