topic Re: Inter-record functions question in QlikView

Inter-record functions question

Wed, 01 Apr 2015 21:27:58 GMT

Hi all. I have a bar graph with the X-axis sorted by priority. I am expecting the graph to have the largest Y-values for the highest priority (lowest integer value). So I'm expecting the left side of the graph to have the highest values and for it to taper off to the right as you go down priority.

I want to draw a second line on the graph that shows the expected values based on a weight, where I take 85% of the first value, and the second should be 85% of that, and so on.

So let's say my data table looks like this:

LOAD * INLINE

[Priority, Value

1, 50

2, 45

3, 40

4, 30

5, 20

6, 10

7, 5

8, 3

9, 2

10, 1

];

And I have a chart where the dimension is Priority, and the expression is Sum(Value). This will create a nice decreasing graph. I want to create a second expression where the value for X=1 is 100, X=2 is 100*.85 = 85, X=3 is 100*.85*.85 = 72.25, and so on like this:

X | Value

1 | 100

2 | 85

3 | 72.25

4 | 61.41

5 | 52.2

6 | 44.37

7 | 37.71

8 | 32.06

9 | 27.25

10 | 23.16

How can I make this expression that keys off of the first value in the previous expression, and sequentially multiplies that value times 85%?

Re: Inter-record functions question

fred_s — Wed, 01 Apr 2015 21:55:17 GMT

Hi Eric,

See attachment

Grtz Fred

Re: Inter-record functions question

Thu, 02 Apr 2015 03:24:42 GMT

Great! That's exactly what I was asking for. Thanks a lot!

So here's a little twist

Let's say instead of multiplying everything by .85 of the first value, I use a weight instead, so my new table looks like this:

LOAD * INLINE

[Priority, Value, Weight

1, 50, 1

2, 45, .8

3, 40, .5

4, 30, .2

5, 20, .1

6, 10, .1

7, 5, .1

8, 3, .1

9, 2, .1

10, 1, .1

];

So I thought I could just replace the .85 in your valcalc expression with Weight, but it only shows the first value then everything else is null. How could I modify that expression to use a pre-defined weight instead of my hardcoded 85%?