Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Separate set analysis statements in same expression?

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
YearMonthCost
2014Jan50
2014Feb100

                                              

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.

  1. 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.
  2. Use IF statements around my calculated field.

Thank you for any insight and assistance!

7 Replies
Not applicable
Author

Kris,

have you tryed:

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


reg

D

magavi_framsteg
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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
magavi_framsteg
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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.

Not applicable
Author

This should work:

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


this may be in variable:


<Month={Jan}>



Not applicable
Author

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.