Synthetic Fields as Dimension

[Qvmaster2019]

Using the same synthetic fields as dimensions, I have this.  It is like "1:Many".

#1

                             

=ValueList('John','Mary','Joseph')	=ValueList('John','Mary','Joseph')	1
John	John	1
John	Mary	1
John	Joseph	1
Mary	John	1
Mary	Mary	1
Mary	Joseph	1
Joseph	John	1
Joseph	Mary	1
Joseph	Joseph	1

But what I want is this (more like 1:1) :

#2

           

=ValueList('John','Mary','Joseph')	=ValueList('John','Mary','Joseph')	1
John	John	1
Mary	Mary	1
Joseph	Joseph	1

I can achieve #2 if I move one of synthetic fields to become expression but I need both to be in dimension.

Another restriction,  values inside the valuelist function will become dynamic later on depending on various selection. This means I cannot do this in load script.

Anyway to achieve this?

[Qvmaster2019]

This is what I have so far, I edit one of the valuelist to be a little bit different. Then modified the expression like this.

=if(
ValueList('Mary','Joseph','John')='Mary' AND if(ValueList('John','Mary','Joseph')='Mary',1,0),
1,
if(
ValueList('Mary','Joseph','John')='Joseph' AND if(ValueList('John','Mary','Joseph')='Joseph',1,0),
2,
if(
ValueList('Mary','Joseph','John')='John' AND if(ValueList('John','Mary','Joseph')='John',1,0),
3,
)
)
)

If there is any other way, kindly post here.

[ganeshsvm]

Hi Darwin,

Since you are using the same values in both Synthetic Dimension, the chart is not able to identify from your expression which dimension you are making conditional and you have used ValueList('Mary','Joseph','John')='Mary' in your expression which doesn't match with the synthetic dimension(order is not the same), so it wont be able to identify the exact dimension.

You can use Synthetic dimension like this in your case to get your expected result,

Dim1 = Valuelist('Mary, 'Joseph', 'John') and Dim2 = Valuelist('John', 'Mary, 'Joseph')

and use the same expression which you have used to get the expected result.

Regarding dynamic values, you need to elaborate more on your requirement.

Hope this helps.

-Ganesh

[Qvmaster2019]

For dynamic values, this is  of no concern so far. I will just have the constants inside the valuelist become variables.

For the changing of dimensions definition, so far, I have the same approach but if there is other, i will be glad to check it out.

[ganeshsvm]

Since you quoted this,

For the changing of dimensions definition, so far, I have the same approach but if there is other, i will be glad to check it out.

Just to confirm, have you already tried the below solution?

Dim1 = Valuelist('Mary, 'Joseph', 'John') and Dim2 = Valuelist('John', 'Mary, 'Joseph')

[Qvmaster2019]

darwin.natividad wrote:

This is what I have so far, I edit one of the valuelist to be a little bit different. Then modified the expression like this.

=if(
ValueList('Mary','Joseph','John')='Mary' AND if(ValueList('John','Mary','Joseph')='Mary',1,0),
1,
if(
ValueList('Mary','Joseph','John')='Joseph' AND if(ValueList('John','Mary','Joseph')='Joseph',1,0),
2,
if(
ValueList('Mary','Joseph','John')='John' AND if(ValueList('John','Mary','Joseph')='John',1,0),
3,
)
)
)

If there is any other way, kindly post here.

Yes, I did. Notice my expression, it was made possible because of minor changes in the dimension definition.

Changing the dimensions alone will not cut it out; it will still display "1:Many "

[ganeshsvm]

BTW I could achieve the solution which you wanted,

I used this Dimensions:

Dim1: ValueList('John', 'Mary', 'Joseph') and Dim2: ValueList('Mary', 'Joseph', 'John')

& used this Expression:

=if(
ValueList('Mary','Joseph','John')='John' AND ValueList('John','Mary','Joseph')='John',1,

IF(ValueList('Mary','Joseph','John')='Mary' AND ValueList('John','Mary','Joseph')='Mary',1,

if(ValueList('Mary','Joseph','John')='Joseph' AND ValueList('John','Mary','Joseph')='Joseph',1,
)
)
) 

and chart properties, Suppress Zero values & Missing values.

[Qvmaster2019]

Yes, this is what I have as well.

[ganeshsvm]

Then I guess you have your solution or what you else do you expect?

[vinieme12]

Why are you using valuelist() ? why not load an inline table and have it associated with your FACT to have a Dynamic solution?

Valuelist() will be very tedious to work with