topic Set Analysis Challenge in QlikView

Set Analysis Challenge

Fri, 19 Nov 2010 19:04:14 GMT

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!

Set Analysis Challenge

Fri, 19 Nov 2010 19:53:32 GMT

Hi,

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

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

Regards, Roland

Set Analysis Challenge

Fri, 19 Nov 2010 21:25:41 GMT

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

Set Analysis Challenge

johnw — Fri, 19 Nov 2010 22:10:15 GMT

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])

Set Analysis Challenge

Fri, 19 Nov 2010 22:17:04 GMT

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

Set Analysis Challenge

Fri, 19 Nov 2010 22:39:39 GMT

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.