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
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.
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.
