Year to Year Comparison not working

[#1]

I'm sure this is something simple but i can't get this set analysis to work.

I'm trying to sum the Quantity invoiced in the same period the previous year using the code

Sum({$<[Order Date] = {'>=$(=date(addyears(min([Order Date]),-1)))<$(=date(addyears(max([Order Date]),-1)))'}>}[Quantity Invoiced])

I've used variables in a text object to test the;

=date(addyears(min([Order Date]),-1))

and

=date(addyears(max([Order Date]),-1))

and they're showing correctly in the text object. However, the set analysis isn't working.

If i sum without set analysis it works fine.

Not sure if it makes a difference but users are selecting the date using the field Month which is created in the script by MonthName([Order Date])

Thanks,

[#2]

I have also tried

Sum({$<Year={$(=Year-1)}>}[Quantity Invoiced])

which also doesn't work.

I attached the table layout in case i've missed something

[Jason_Michaelides]

Try replacing your ' with "

Sum({$<[Order Date] = {">=$(=date(addyears(min([Order Date]),-1)))<$(=date(addyears(max([Order Date]),-1)))"}>}[Quantity Invoiced])

Hope this helps,

Jason

[jonathandienst · Accepted solution]

Hi

In addition, I think you will need to override some or all of the other calendar fields:

Sum({$<[Order Date] = {">=$(=date(addyears(min([Order Date]),-1)))<$(=date(addyears(max([Order Date]),-1)))"}, Year=, [Week Number]=, Month=>}[Quantity Invoiced])

Regards

Jonathan

[#5]

Thanks Jason,

I've tried this and that's not working either. Just trying to make a copy that i can upload as data is sensitive.

Regards,

Nick

[#6]

Ok,

I think there was an issue with the join for the Order Date to Order Number. I've made the fields distinct from the table and Jonathan's idea has worked.

Thanks very much for your help on this everyone!!

Regards,.

Nick