Set analysis with If statement creating a list to compare with

[dirk_fischer]

Hi Folks,

I try to do a set anaylsis, where I compare a dimension based upon the result of an If Statement to a list of values.

I tried different variations of =Only({<COL={$(=If($(vLeftCol)=1,'1,3,5','2,4,6'))}>} COL), but didn't succeed.

In principle the Dimension COL can have values from 1 to 6 and if the variable vLeftCol = 1, then I Need the Statement to return 1, 3, 5 and otherwise it Needs to return 2, 4 and 6.

It would be great, if somebody had an idea how to solve this.

Best regards,

Dirk

[tresesco]

Where do you want to use this expression? KPI or chart expression or a calculated dimension? A bit of context would be helpful.

[dirk_fischer]

I want to use the set Analysis in a KPI (gauge) and I need to filter the correct columns. In order to make it easier understandable, I extracted the relevant part.

[dirk_fischer]

If  $[vLeftCol)=1, then I want to get the results as shown in the table below 🤔

COL	Only({<set analysis>} COL
1	1
2	-
3	3
4	-
5	5
6	-

[tresesco]

With only this condition, you should use if statement, that would neither require set analysis nor only(). The way you are trying to write the set expression - seems would not work. If you could share a sample app with data and explain the complete expression scenario, we might help better.

[dirk_fischer]

Okay I try to explain it in a different way.

What I want to do is a set analysis, where I compare the Dimension COL depending on the value of the variable $(vLeftCol).

So if $(vLeftCol)=1, I want to have the set analysis doing COL={1,3,5) and otherwise I want to have the set Analysis checking for COL={2,4,6).

The full Expression is pretty lengthy, so I would prefer not to do it with an if statement enclosing it.

 The Expression Only({<COL={$(=If($(vLeftCol)=1,1,2))}>} COL), but I would like to have it working for 3 and 5 as well.

[dirk_fischer · Accepted solution]

I found the solution.

Simply 

=Only({<COL={$(=If($(vLeftCol)=1,'1,3,5','2,4,6'))}>} COL)

is doing the trick.

Thank you very much for your help.

[tresesco]

Define another variable like:

vCondSet=If($(vLeftCol)=1, '1,3,5', '2,4,6'))

and then use this vCondSet in set analysis.