topic Re: Syntx issue with Set Analysis in QlikView

Syntx issue with Set Analysis

Wed, 02 Mar 2016 18:01:50 GMT

Hello,

I'm having a syntax issue with a set analysis formula I am trying to create. The formula is trying to SUM invoices where dates fall between 2 dates and the posting date is greater than the end date. I've created 2 variables and I know these variables are correct because I've checked them.

=Sum({$<[Invoice Header.Order Creation Date] = {>= (vFromDate) <= (vToDate)}, [Date] = {> (vToDate)}>}[Invoice Line.Amount Including VAT])

vFromDate definition:

=(date(MonthStart(Max([Invoice Header.Order Creation Date])) - 365))

vToDate definition:

=(date(MonthEnd(Max([Date])) - 365))

When I use these variables alone they work but when I add them to the formula I get no results I think it's a syntax error but I don't see anything wrong with my formula.

Can someone help?

Re: Syntx issue with Set Analysis

petter — Wed, 02 Mar 2016 19:31:29 GMT

You seem to be lacking $-signs in front of the variables and furthermore you should also enclose the search string in double quotes:

=Sum({$<[Invoice Header.Order Creation Date] = {">=$(vFromDate)<=$(vToDate)"}, [Date] = {">$(vToDate)"}>}[Invoice Line.Amount Including VAT])

Re: Syntx issue with Set Analysis

petter — Wed, 02 Mar 2016 19:35:32 GMT

There is a slight inaccuracy in your calculation as not every year has 365 days. To be entirely accurate you would have to do something like this instead:

vToDate definition:

=Date( AddYear( MonthEnd( Max( [Date] ) ), -1) )

Actually since you will then do only date calculations you should be able to simplify it into this:

= AddYear( MonthEnd( Max( [Date] ) ), -1 )

And similarly with the other variable...