Set Analysis Trouble with Dual Field

[#1]

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

[whiteline · Accepted solution]

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. 

[jerem1234]

In addition, you can also use:

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

Hope this helps!

[#4]

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

Like,

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

[#5]

Thanks for clearing this up for me.

[#6]

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