Sum with AddYears in condition

[Paulonst]

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?

[dplr-rn]

that is odd. 

try formatting about the addyears function to your date format

[Paulonst]

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

Can you please explain better your suggestion?

[dplr-rn]

try this

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

[Paulonst]

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

[dplr-rn]

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

[Kushal_Chawda · Accepted solution]

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)

[Paulonst]

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