Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ![]() |
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.
Thank you for any insight and assistance!
Kris,
have you tryed:
SUM({1<Month={Jan}, Year={2014}>} Cost)
reg
D
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
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 |
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.
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.
This should work:
SUM({<Month={Jan}><Year={2014}>} Cost)
this may be in variable:
<Month={Jan}>
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.