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?

[sebastiandperei]

Hi Whiteline, I've understood that the question was if where possible to create a synthetic dimention (this kind of calculated dimention) with the "IF" sentence. It's not possible.

Use the IF sentence in the expression, using simple calculated dimentions where available from some earlier versions of QV.

a_i_r, i haven't found the way of use two Valueloop's in a expression. So, you can use a Dispersion Chart:

Dimmentions: fieldX, fieldY

In expressions tab, it will have a different sctructure. In x put fieldX, in y fieldY, and in expression, the field that you have to show the value. See attached.

[whiteline]

Sebastian, I didn't know that The logical solution is a label reference but it doesn't work.

The question was about integration. And you said "it is not possible to use "if" function with synthetic dimmentions."

So I said it's possible.

The farther problem is that you can't distinguish two equal dimensions with similar valueloops.

But it's possible to make them a bit difference for QV but the same for the user.

[sebastiandperei]

Yes whiteline, i was wrong understanding the question... Sorry, my english is not good... (Im from Argentina)

I wanted to say that is not possible to conditioning a synthetic dimention, which has ValueLoop if one condition, or other valueloop with other condition (f.e. If (Rate>10, valueloop(0,100), valueloop (0,10) in a calculated dimention)... I didn't know the problem that a_i_r had.

[whiteline]

Sebastian, mine too. I'm from Russia

The point is that we've found the solution.

[#6]

Whiteline,

I don't quite understand how this is solved yet.

If I use valueloop twice to generate the 2 axes, then how do I input my expression so that the cell colour considers my risk dimensions?

I've attached a picture to demonstrate what I have now - if I use Valueloop instead of my 2 dimensions, then the expression (presumably) no longer works, as it cannot locate a Risk 1/Risk 2 combination to test against?

[whiteline]

It would be match easier if you type the expression that you use now for colorizing.

For now, consider this:

=Sum(if(ValueLoop(0,100)=Dimension, Value, null()))

It's expression for getting Sum(Value) with Dimension.

[sebastiandperei]

Did you tried with dispersion chart?

If you send a reduced copy, we could help you better!!

[#9]

Sebastian,

I understand what you're thinking - this was also my first idea for this object, but if there are lots of combinations in close proximity to each other, I will only see the marker at the top (there are roughly 300,000 underlying value combinations in the data which I aggregated to the risk dimensions). Visually, this will not achieve what I need.

whiteline,

Your suggestion seems to suggest only one dimension again, whereas I need two.

Maybe what I need simply isn't possible?....

[whiteline]

Now you have two possible solutions.

Sebastian suggests you replace your table with almost the same looking scatter chart as it has possibility to use Continuous dimensions.

The only difference is that it contains circles instead of squares.

I suggest you to use calculated dimension in your table.

Try something.