topic Problem with set modifier with embedded calculation in QlikView

Problem with set modifier with embedded calculation

pjn123pjn123 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Problem with set modifier with embedded calculation

marcus_sommer — Tue, 28 Feb 2017 10:58:02 GMT

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

Re: Problem with set modifier with embedded calculation

pjn123pjn123 — Tue, 28 Feb 2017 12:17:49 GMT

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

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