Set Analysis with expression

Mon, 22 Sep 2014 13:29:56 GMT

Not sure why I can't get this to work...

=Sum({$<Det.PostingDate_Month|Year={$(=addmonths(max(Det.PostingDate_Month|Year),-1))}>Det.NetAmt)

I want to sum the Det.NetAmt for 1 month prior to my month|year field

Any Ideas? I'm getting an error in set modifier ad hoc eliment list.

Much thanks,

-David

Re: Set Analysis with expression

JonnyPoole — Mon, 22 Sep 2014 13:36:20 GMT

=Sum({$<Det.PostingDate_Month|Year={$(=addmonths(max(Det.PostingDate_Month|Year),-1))}> } Det.NetAmt)

I noticed a missing closing '}'

Also i suggest enclosing that field name in square brackets : []

=Sum({$<[Det.PostingDate_Month|Year]={$(=addmonths(max([Det.PostingDate_Month|Year]),-1))}> } [Det.NetAmt])

If that doesn't help, maybe try calculating the addmonths() expression in a variable, confirming the variable value and using the variable in the SET MODIFIER. Something like this:

=Sum({$<[Det.PostingDate_Month|Year]={$(vPriorMonth) }> } [Det.NetAmt])

topic Set Analysis with expression in QlikView

Set Analysis with expression

Re: Set Analysis with expression