How to force dimension values in a pivot table

[#1]

Hi All,

I have built a pivot table containing data from two fields, which I use as a sort of Weather Chart (i.e. I use an expression for the cell colour, but no expression is used in the normal expression field).

At the moment, the values in these dimensions are interspersed between 1 - 50, so when I make a pivot table, if there is no value in the data set, then there is no value shown in the dimension. As a result, each dimension could look like 1  2  5  6  7  9  15  20 and so on, which makes the chart visually inconsistent, depending on the selection/s made by the user.

What I want to do, is specify that the range of values shown in the pivot axes should be 0 - 100 (whole numbers) for both.

This must be in the dimension definition though, as I cannot amend the script...

Any ideas?

[Anonymous]

I've a solution that work only if you have numeric in your dimension.

Use ValueLoop(n,m) as dimension, then in the indicator, test if it's equal to your real dimension field.

If no, give the indicator a value near zero (but not zero).

See attached sample.

If it's not numeric you could use valuelist fct, but the value have to be fixed and if you have a lot of values it could be tedious, as you don't have access to the script.

[#3]

sduguet,

Good idea, but your example uses only one dimension (the valueloop), and since my chart is a pivot, I have 2 dimensions to consider. Is there some way to integrate Valueloop with an IF statement?

Or any other ideas?

[jonathandienst]

Hi

Assuming that the dimension is a field (called dimensionfield below) and needs values from 1-50, add something like this to the load script

LOAD RowNo() As dimensionfield

Autogenerate 50;

The gaps are caused by missing values in dimensionfield. This will ensure that all the values exist. You will also need

to disable the suppress zero and suppress missing values options on the chart. (The load statement should not have a join/keep or concatenate qualifier).

If the dimension is calculated, you may want to move the calculation into the load script to calculate all the values.

Hope that helps

Jonathan

[#5]

Jonathan,

The problem is that I cannot edit the script for this report - and for now I think this is a one-off item - so is it possible to "build" this dimension using an expression?...

[jonathandienst]

Hi

Sorry, didn't see that in your original post.

In that case, I would try the following:

• Create a new model
• In the script, first insrt a binary load of the model (this must be the first statement in the script). Use

               Binary origmodel.qvw;

• Then add the load statement from my earlier post.
• Run the reload
• Now copy and paste the sheet objects from the original model to the new model (select a sheet, press Ctl-A Ctl-C and then in the new model, add a sheet and press Ctl-V)
• Save the new model.

Hope that helps

Jonathan

[sebastiandperei]

Hi a_i_r__,

I think that Jonathan suggestion is the best, but if you can't do it (not everyone in a company has access to the .qvw file), i will try to help you to make the pivot table anyway.

The suggestion of sduguet is that you need. So, if you have more than one Dimention, it works like the same way. For example, if you have two dimentions:

1) Customer

2) =ValueLoop(1,3)

The table will show each customer, with 3 values in second column.

1) =ValueLoop(1,3)

2) Customer

The table will show, for 1, all customers. For 2, all customers again. For 3, the same...

So, as your question said, no, it is not possible to use "if" function with synthetic dimmentions. What do you need with these dimentions?

[whiteline]

Everithing's possible!!!

Use as calculated dimension: =ValueLoop(0,100)

As expression use something like this:  =Sum(if(ValueLoop(0,100)=Dimension, Value, null()))

Where  Dimension is your dimension with gaps.

Awesome, it works (QV11)!!!

[#9]

Hi whiteline,

That's along the lines of what I was thinking, but this would only cover a single dimension, whereas I have two.

In essence, I have built a pivot table that shows two risk dimensions - one along the "x" axis and one along the "y" axis. Rather than use an expression with values, I have used the colormix function to shade the cells (red/yellow/green) according to the level of risk for that combination of values.

It looks fine, but because there are values missing on each axis (values are discrete and fall between 0-100, but not every value is used), the diagram does not really represent the data correctly.

[whiteline]

I understand.

In your case you have to distinguish two calculated dimensions

I think you can use =ValueLoop(0,100)  and =ValueLoop(0,101) for example.

And then in expressions you can force  null all values with 101 (so that this column/row disappears).