how to get values for $Field

[kristof_j]

Hi

Is there a way to get values when you have $Field as dimension?

If I make an alternate state I want to see the largest differences for almost all the fields.

I have the expressions I need but they only work if you select one field. But this is inefficient if you have many fields.

[kristof_j]

no suggestions?

[Anonymous]

Hi

Linking $Field to individual column is really nightmare. Can you load data with cross table join and create field with all columns. You can then link data with individual columns.

CrossTable (Field, Data, 1)

LOAD * INLINE [

    TransId, Dim1, Dim2, Dim3

    1, A, a, 1

    2, B, b, 2

    3, C, c, 3

];

[tresesco]

Like this attached sample?

Note the enabling condition in the dimension tab, like: =Index(Concat(DISTINCT $Field),'Dim1')

[kristof_j]

I added your table to the model but I don't understand what you mean.

In 'real' model we have over 100 field so it should also be automated

I added a new QVW with your suggested table.

[Anonymous]

See attachment

[kristof_j]

The dimensions should be on the same level instead of different levels.

What we want is a table that indicates in which dimensions or values of dimensions the largest differences are between 2 groups.

The groups could be defined in an alternate state.

[kristof_j]

Good suggestion but we still need to select on the dimensions. In our model we have +4million customers and 144 fields and some field can contain over 100 different values.

[marcus_sommer]

I'm not sure if it what you want - but I think it will be useful. Have a look on the sheet "Datastructure" within the attachment. Be careful with formulas like "Concat Fieldvalues" on larger apps - they need a lot of performance. For this you could use calculation-conditions on getselectedcount($Table or $Field) or similar.

- Marcus

[kristof_j]

We want to do some calculations for the fieldvalues.

You can compare it with a piot table but with all the dimensions on the first level.

If you add a dimension in a pivot table it will be on a lower level.

something like this with state1-2 and difference as expressions:

Field     Value     state1     state2     difference

A               1          10          10               0

                  2          15          5               10

B               1          0              5               -15

...

and not like an ordinary pivot table:

Dim A     Dim B      state1     state2     difference

     1               1          0               5               -5

     1               -          10               5               5

     2               1          0                   ...