Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Challenge

Forum - I couldn't think of a better way to title this subject other than a challenge. The easiest way to explain my challenge is to show you a snippet from my expression:

=only({$<[Quarter] = {'$(=maxstring([Quarter]))'},[MeasureA] = {'A'}>} [ValueA])

This returns a single value, which is fine, but the challenge is that I need this value to not change for each [Quarter]. So for example when no [Quarter] are selected it returns 26 (2010 Q4), but when (2010 Q3) is selected or displayed the value changes because the function maxstring changes to (2010 Q3). I can't simply add [Quarter]= to my set analysis because I'm already finding the latest [Quarter] using the function. It's like a reverse negative if that makes sense.

Any idea's on how to remedy? My only thought is if I could nest a set expression WITHIN a set expression. In sudo code it would be "Find the maximum quarter and retrurn the value ignoring the current quarter selections".

I will attach an example with illustrates my problem. Any ideas would be appreciated. Until than the only way I can think of getting around this is hardcoding or using hardcoded variables which I don't want to do.

Thanks!

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

if I understand you right, alter the expression in your textbox this way:

=only({$<[Quarter] = {'$(=maxstring( {1} [Quarter]))'},[MeasureA] = {'A'}>} [ValueA])


Regards, Roland

View solution in original post

5 Replies
Not applicable
Author

Hi,

if I understand you right, alter the expression in your textbox this way:

=only({$<[Quarter] = {'$(=maxstring( {1} [Quarter]))'},[MeasureA] = {'A'}>} [ValueA])


Regards, Roland

Not applicable
Author

Roland your response was much appreciated. So this is great and something I didn't know you could do (embedding the ALL (1) set variable within another. Good news is that in a simple text box this works perfectly, bad news is that in a Chart where we want to display the Quarter with the output (26) doesn't work (see below). We would agree it's because the ALL function {1} you added to my original expression will ONLY take the latest Quarter which is FY2010 Q4. So when try and display this in a chart over time only the value for FY2010 Q4 displays.

So visually, looking at my attached example, the text box below works when you plug in your updated expression but the graph doesn't. I'm HOPING there is some way to tweak the expression so I can get bars of value 26 in the red arrows ...

You have any other ideas? Even if not again I really appreciate the feedback. - DB

johnw
Champion III
Champion III

Adding "total" may give you what you want (it tells it to ignore the dimension value):

only({$<[Quarter] = {'$(=maxstring( {1} [Quarter]))'},[MeasureA] = {'A'}>} total [ValueA])

Not applicable
Author

HI again,

if you want to use it in your chart, you can use the "TOTAL" similar to this:

<pre>=only({$<[Quarter] = {'$(=maxstring( {1} [Quarter]))'},[MeasureA] = {'A'}>} TOTAL [ValueA])

This should work in your chart. Look at my attachment.
RR
Not applicable
Author

Roland & John,

Spot on! Yes the TOTAL qualifier works like a charm. We're good to go. Again I appreciate the time you took to help, I learned something new and accomplished something at the same time. Take care.