topic Re: How to force dimension values in a pivot table in QlikView

How to force dimension values in a pivot table

Fri, 31 Aug 2012 10:20:09 GMT

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?

Re: How to force dimension values in a pivot table

Anonymous — Fri, 31 Aug 2012 10:58:16 GMT

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.

Re: How to force dimension values in a pivot table

Fri, 31 Aug 2012 11:55:46 GMT

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?

Re: How to force dimension values in a pivot table

jonathandienst — Fri, 31 Aug 2012 12:26:25 GMT

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

Re: How to force dimension values in a pivot table

Fri, 31 Aug 2012 13:11:57 GMT

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

Re: How to force dimension values in a pivot table

jonathandienst — Sat, 01 Sep 2012 11:02:09 GMT

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

Re: How to force dimension values in a pivot table

sebastiandperei — Sat, 01 Sep 2012 15:13:02 GMT

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?

Re: How to force dimension values in a pivot table

whiteline — Tue, 04 Sep 2012 05:42:42 GMT

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

Re: How to force dimension values in a pivot table

Tue, 04 Sep 2012 07:40:17 GMT

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.

Re: How to force dimension values in a pivot table

whiteline — Tue, 04 Sep 2012 07:55:33 GMT

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

Re: How to force dimension values in a pivot table

sebastiandperei — Tue, 04 Sep 2012 15:09:05 GMT

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.

Re: How to force dimension values in a pivot table

whiteline — Tue, 04 Sep 2012 15:37:05 GMT

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.

Re: How to force dimension values in a pivot table

sebastiandperei — Tue, 04 Sep 2012 21:00:41 GMT

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.

Re: How to force dimension values in a pivot table

whiteline — Wed, 05 Sep 2012 07:46:10 GMT

Sebastian, mine too. I'm from Russia

The point is that we've found the solution.

Re: How to force dimension values in a pivot table

Wed, 05 Sep 2012 08:55:59 GMT

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?

Re: How to force dimension values in a pivot table

whiteline — Wed, 05 Sep 2012 14:49:53 GMT

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.

Re: How to force dimension values in a pivot table

sebastiandperei — Wed, 05 Sep 2012 22:04:00 GMT

Did you tried with dispersion chart?

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

Re: How to force dimension values in a pivot table

Thu, 06 Sep 2012 13:16:19 GMT

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

Re: How to force dimension values in a pivot table

whiteline — Thu, 06 Sep 2012 14:54:24 GMT

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.