topic Re: compare columns within a table in Visualization and Usability

compare columns within a table

beck_bakytbek — Tue, 16 Oct 2018 09:55:37 GMT

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

Re: compare columns within a table

petter — Tue, 16 Oct 2018 10:09:39 GMT

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)

Re: compare columns within a table

beck_bakytbek — Tue, 16 Oct 2018 10:20:19 GMT

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) ?

Re: compare columns within a table

petter — Tue, 16 Oct 2018 10:53:45 GMT

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

Re: compare columns within a table

beck_bakytbek — Tue, 16 Oct 2018 11:17:56 GMT

Hi Peter,

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

Have a nice day