Problem with set modifier with embedded calculation

[pjn123pjn123]

Hi everyone

I'm new to QlikView and I'm having difficulty in getting my expression to give me the correct value

I'm having problems with the set modifier below:

//Works 100% - just a bit slow
sum({1} If([YearMonth]<= $(=date(addmonths(makedate(Year,Month),0),'YYYYMM')) and [YearMonth]> $(=date(addmonths(makedate(Year,Month),-12),'YYYYMM')) and [Account Code] >= 5000 and [Account Code] <= 6010 and [Acc Advisor Code] <> '#0000' and [Acc Advisor Code] <> '#', Amount*_ActualInd))

//Works 100%
sum({1 < [YearMonth] = {">201512<=201612"}, [Account Code] = {">=5000<=6010"}, [Acc Advisor Code] -= {'#0000', '#'} >} Amount*_ActualInd)

//Gives me ZERO values
sum({1 < [YearMonth] = {$(= ">" & date(addmonths(makedate(Year,Month),-12),'YYYYMM') & "<=" & date(addmonths(makedate(Year,Month),0),'YYYYMM'))}, [Account Code] = {">=5000<=6010"}, [Acc Advisor Code] -= {'#0000', '#'} >} Amount*_ActualInd)

Thanks in advance for anyone that can help me in the right direction!

[marcus_sommer · Accepted solution]

Try it this way:

sum({1 < [YearMonth] =

{">$(=date(addmonths(makedate(Year,Month),-12),'YYYYMM'))<=$(=date(addmonths(makedate(Year,Month),0),'YYYYMM'))"},

[Account Code] = {">=5000<=6010"},

[Acc Advisor Code] -= {'#0000', '#'} >} Amount*_ActualInd) 

- Marcus

[pjn123pjn123]

That was a quick fix! Thanks for the help Marcus!

The code now returns the last 12 month figures as expected.