topic Re: Set Analysis Trouble with Dual Field in QlikView

Set Analysis Trouble with Dual Field

Fri, 28 Feb 2014 18:55:33 GMT

Hi,

I'm having trouble using set analysis with a dual field. I've attached a simple data structure that illustrates the problem. I have two fields, One and Two, that look like

One,Two

A,1

A,2

A,3

A,4

B,5

B,6

B,7

B,8

I have used the dual function in my load script,

If(One='A',dual(One,1),dual(One,2)) as One

I am having trouble using the number portion in set analysis, though. To elaborate:

Sum({<One={'B'}>}Two) works fine (returns 26);

Sum({<One={"=One=Max(TOTAL One)"}>}Two) works fine (returns 26);

Sum({<One={'A'}>}Two) works fine (returns 10);

but

Sum({<One={"=One=(Max(TOTAL One)-1)"}>}Two) returns 10 if nothing or everything is selected for One but 0 if either A or B is selected. I want the expression to return 0 only if just A is selected, and otherwise 10.

Please find attached an illustration of my predicament. Any help will be greatly appreciated

Many thanks,

Ryan

Re: Set Analysis Trouble with Dual Field

whiteline — Fri, 28 Feb 2014 19:15:36 GMT

Hi.

The problem is not with dual but with the set expression.

Lets simplify it: =Sum({<One={"=One=1"}>}Two)

With your default selection 'One=B=2' it won't work because there is no '1' in current selections.

You have to use set again to clear the selection and some function that does nothing just applies the set.

=Sum({<One={"=Only({1} One)=1"}>}Two)

Second, the total modifier won't work here it's suitable only for chart dimensions.

But you need somehow to calculate the max total. One possible solution is to use $-sign expansion. QV expands it once before calculating the expression.

=Sum({<One={"=Only({1} One)=$(=Max(One))-1"}>}Two)

Although, you will possibly need the total modifier if decide to use it in some chart.

Re: Set Analysis Trouble with Dual Field

jerem1234 — Fri, 28 Feb 2014 19:22:14 GMT

In addition, you can also use:

sum({<One={'>=$(=max(One)-1)<=$(=max(One)-1)'}>}Two)

Hope this helps!

Re: Set Analysis Trouble with Dual Field

Fri, 28 Feb 2014 19:23:41 GMT

Just a suggestion. Why don't you use min instead of max for A.

Like,

Sum({<One={"=One=Min(TOTAL One)"}>}Two)

Re: Set Analysis Trouble with Dual Field

Fri, 28 Feb 2014 19:37:47 GMT

Thanks for clearing this up for me.

Re: Set Analysis Trouble with Dual Field

Fri, 28 Feb 2014 19:38:55 GMT

That's pretty clever. Works like a charm. Thanks.