compare columns within a table

[beck_bakytbek]

Hi Folks,

i got a following Situation, my data does look like:

type_of_action, comp1, comp2, comp3, comp4

action1,                   x          x          x          x

action2                    x          x                      x

action3                    x                    x

action4                    x                                   x

my expected Output is to create the additional column with the feature like this

type_of_action, comp1, comp2, comp3, comp4    Control

action1,                   x          x          x          x                4

action2                    x          x                      x                3

action3                    x                    x                              2

action4                    x                                   x               2

does anybody have any ideas, how to resolve this issue?

Thanks a lot

Beck

[petter]

You simply have this expression as a measure you can label Control in a table that has type_of_action as a dimension and the comp1 to comp4 as separate measures:

=Count(comp1)+Count(comp2)+Count(comp3)+Count(comp4)

[beck_bakytbek]

Hi Peter,

first of all, thanks a lot for your Feedback, at the beginning i solved this ussue by using of this Expression (like your suggestion), but then i thought, can i create from such Expression a Dimension (like 4, 3 , 2 ,1). Do you have any idea, how to create the columns or Dimension ( like 4 ,3, 2, 1) ?

[petter · Accepted solution]

You can use the CROSSTABLE prefix of the LOAD statement to unpivot your data to create a dimension that tracks the "comp". Then you could use the Pivot Table or the Table in the UI to calculate the Control as a normal aggregation.

This would be a better data model for BI and analytics purposes.

CrossTable( comp , value ) LOAD

  type_of_action,

  comp1 AS [1],

  comp2 AS [2],

  comp3 AS [3],

  comp4 AS [4]

FROM

   ..... ;

In av Pivot Table you would use type_of_action and comp as dimensions and put the comp as a column not as a row. Finally you would create this expression as a measure:

Count( value )

In av regular/straight table you would have to add the type_of_action as a dimension but add the comp for each type manually as expressions like this:

=Count( {<comp={1}>} comp )

=Count( {<comp={2}>} comp )

=Count( {<comp={3}>} comp )

=Count( {<comp={4}>} comp )

And then finally:

=Count( comp )

for the Control

[beck_bakytbek]

Hi Peter,

thanks a lot for your time and help, it does work great

Have a nice day