Key figures as first dimension of trellis chart

[#1]

Hello,

I am trying to build a trellis chart with 2x2 diagrams. My problem is that I dont seem to be able to get the 4 key figures I have created (one is a simple sum, the other three are distinct counts) into the four diagrams of the trellis chart.

What I am trying to get is a comparison of the four different key figures as a pie chart with a common navigation / formating / breakdown.

Does anyone have a suggestion how to get this working?

Cheers,

Danny

[swuehl]

Hi Danny,

so what have you set as dimension and expressions in your pie chart with activated trellis?

You probably could set an artificial dimension / valuelist() and use the actual dimension value to pick() your expression.

I am not sure however if this is the best approach, doesn't the trellis chart use a common legend?

Maybe separate pie charts would be more appropirate with your multiple expressions?

Regards,

Stefan

[#3]

Hello Stefan,

The common legend and formating is exactly what I need. I have tried four pie charts, but the colors and ordering are all different. Further, I like the fact that the trellis chart animates all charts the same (you hover over one breakdown and it is emphazised on all charts).

Currently I have set it up like:

Dimension: The (single) desired dimension for breakdown

Expression: The four key figures

I want to to get to a chart which looks like:

key figure 1 | key figure 2

----------------|------------------

key figure 3 | key figure 4

They would all be pie charts. I would like to compare the following:

Several departments are using reports. I want a breakdown of unique users, unique queries executes, unique sessions opened (all three count distinct) and number of navigation steps (sum). The reason is that one department would have only a few queries, but a lot of users using the reports.

Hope that clears up the requirement. If you know about a solution without a trellis chart, but still leaving these charts connected I am happy to hear it.

Regards,

Danny

[swuehl]

Hi Danny,

not sure if I fully understood, maybe something like attached?

I just called the key measures Ka, Kb, Kc and Kd, but that's just names.

I used two dimensions, Key measures and department (I understood that thats your dimension for the single charts, right?).

Then only one expression, which checks the current Key dimension value and picks the correct expression to calculate (you could simplify that with pick() and match() function for more and more advanced Key figures).

Hope this helps,

Stefan

[#5]

Stefan,

Thanks a lot for your help. With your help I have found the correct setup. Especially helpful was the valuelist() as I have tried several things already to get an aray of values in the first dimension, but never succeded. Together with the if statement I was able to display the key figures in the first dimension and format them all the same.

Here the setup:

Dimension:

=valuelist('Users','Sessions','Queries','Navigations')

Expression:

if(valuelist('Users','Sessions','Queries','Navigations')='Users',Count (DISTINCT User),

if(valuelist('Users','Sessions','Queries','Navigations')='Sessions',Count (DISTINCT Session),

if(valuelist('Users','Sessions','Queries','Navigations')='Queries',Count (DISTINCT Query),

Sum ([Nav Counter]))))

The problem is, that three of the key figures are counts on other dimensions, so I do not have them as numbers in a column. The expression setup looks a bit messy with the repitition of the valuelist, but I couldn't reference it via the label and this seems to work too.

Very nice work.

Regards,

Danny

[swuehl · Accepted solution]

Good to hear!

To avoid repetition of the valuelist, look at the pick and match function, should look like:

=pick(match(valuelist(.....),'Users','Sessions','Queries','Whatever4th')

, Count( DISTINCT User)

, ...

, ...

, sum (...)

)

Have fun,

Stefan