Set analysis compare with same period last year

[Hilde]

Hi,

I want to sum last years sales for the same period as  selected for this year  and have the following expression:

=sum({<Datefield= {">=$(=Addmonths(min(Datefield)),-12)<=$(=Addmonths(max(Datefield)),-12)"}> } Sales)

For example, if selected dates are 20190101, 20190201 and 20190301 I want to sum sales between 20180101 and 20180301.

Date field format is 'YYYYYMMDD' and I have one date per month to select from:  20190101, 20190201 and so on.

I can't figure out why the expression is not working, and would be very thankful for any help.

Kind regards, Hilde

[lironbaram]

hi 

you need to make sure 

that the function inside the set analysis returns the date in the same format as your date field 

so try adding date around your function and format it 

[Vegar]

To get addmonths() to work your Datefield needs to be recognized as a date. If you use =Addmonths(MAX(Datefield),-12)
as an expression in your application it should return the correct corresponding date.

Are you making selections in other period fields such as Year, month etc.? If so you will need to cancel out their effect like this: =sum({<Year, Month, Datefield= {">=$(=Addmonths(min(Datefield)),-12)<=$(=Addmonths(max(Datefield)),-12)"}> } Sales)

[Vegar]

Oh, I noticed that your parenthesis are a bit off in the addmonths() function. See adjusted expression below.

=sum({<Datefield= {">=$(=Addmonths(min(Datefield),-12))<=$(=Addmonths(max(Datefield),-12))"}> } Sales)