Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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) ?
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
Hi Peter,
thanks a lot for your time and help, it does work great
Have a nice day