topic Re: Separate set analysis statements in same expression? in QlikView

Separate set analysis statements in same expression?

Tue, 03 Jun 2014 11:58:01 GMT

I am trying to include two set analysis statements in the same expression, one of which is from a variable, and the other that resides in the expression. The last line in the table below is a simple example of what I would like to do.

What I am finding is that if I include two set analysis statements, the expression only looks at the first statement. The two "In question, not working" lines below are examples of this. I'm curious if there is a built-in hierarchy in which QlikView parses the expression, and if there is a way to include more than one separate statement in the same expression.

My actual application has complex expressions and is much more detailed, so to keep things simple included this sample to outline what I am seeing:

Sample dataset
Year	Month	Cost
2014	Jan	50
2014	Feb	100

Typical working expression	SUM({1<Year={2014}>} Cost) = 150
Typical working expression	SUM({1<Month={Jan}>} Cost) = 50
Typical working expression	SUM({1<Year={2014}>}, Month={Jan}>} Cost) = 50
In question, not working	SUM({1<Year={2014}>} {1<Month={Jan}>} Cost) = 100
In question, not working	SUM({1<Month={Jan}>} {1<Year={2014}>} Cost) = 50 (just reversed order)
Real world usage: I want to push the set analysis to a variable to be used to all other expressions, but have the ability to append additional set analysis as needed.
Variable =	vYear = {1<Year={2014}>}
Used in 95% of expressions:	SUM($(vYear) Cost) = 150 (works well, no issues)
5% of expressions with additional criteria. Does not work example :	SUM($(vYear) {1<Month={Jan}>} Cost) = 50 (but returns 100 per example above)

Note that I did consider the following alternatives but I am trying to keep this as slim and efficient as possible due to the amount of expressions this will be used in, so these will not suffice.

Leave the main variable with an open-ended set analysis statement by removing the '>}' and then using another variable to append the closing brackets to the end in each expression, so I could included any other logic between the variables.
Use IF statements around my calculated field.

Thank you for any insight and assistance!

Re: Separate set analysis statements in same expression?

Tue, 03 Jun 2014 12:03:45 GMT

Kris,

have you tryed:

SUM({1<Month={Jan}, Year={2014}>} Cost)

reg

Re: Separate set analysis statements in same expression?

magavi_framsteg — Tue, 03 Jun 2014 12:06:43 GMT

Hi Kris.

You need to make the final expression work with your variable like your third working expression, something like this:

Variable =

vYear = {1<Year={2014}

Pseudo code:

SUM($(vYear), Month={Jan} >} Cost)

//Regards

Magnus Åvitsland

BI Architect

Framsteg AB

Re: Separate set analysis statements in same expression?

Tue, 03 Jun 2014 12:08:55 GMT

This is what I had outlined in my example. I need to use completely separate statements, not just one.

Typical working expression

SUM({1<Year={2014}>}, Month={Jan}>} Cost) = 50

Re: Separate set analysis statements in same expression?

magavi_framsteg — Tue, 03 Jun 2014 12:12:34 GMT

Hi again Kris.

What do you mean by "use completely separate statements"?

Why can't you have several overridden selections in ONE {set}, like in my example above?

*Edit

Kudos for your very NICE post layout!

I've *never* seen it before, and it makes it so much more readable.

Re: Separate set analysis statements in same expression?

Tue, 03 Jun 2014 12:13:09 GMT

Thanks. That is similar to my alternative #1 that will not work because I would have to close off the statement in expressions that only include the single variable. I need to keep the logic streamlined for caching purposes.

Re: Separate set analysis statements in same expression?

Tue, 03 Jun 2014 12:13:19 GMT

This should work:

SUM({<Month={Jan}><Year={2014}>} Cost)

this may be in variable:

<Month={Jan}>

Re: Separate set analysis statements in same expression?

Tue, 03 Jun 2014 12:24:53 GMT

Your example would work, but for 95% of my expressions I would have to append the closing brackets for the same statement, so like this: SUM($(vYear) & '>}' Cost). I'm just trying to get around the added 'mess!' I really would like to understand too why the 'In question' examples return a value and don't error out. That is interesting.