topic Adding calculated fields to Pivot Table in QlikView

Adding calculated fields to Pivot Table

Sun, 18 Dec 2011 17:56:55 GMT

For the object Product Line Total, I would like to calculate the variance between the months for the value Extension for both "R" and "F", but am struggling to figure it out. I have attached the file. As always any assistance is greatly appreciated.

Adding calculated fields to Pivot Table

Mon, 19 Dec 2011 20:30:35 GMT

Assuming you always want [Product Type] R and F, and the variance between them:

1. Drop the dimension [Product Type]

2. Create three expressions and be explicit using SET analysis to get what you need.

Expression for R:

=sum

(

[Product Type] = {'R'}

Extension

)

Expression for F:

=sum

(

[Product Type] = {'F'}

Extension

)

And, the variance:

=sum

(

[Product Type] = {'R'}

Extension

)

sum

(

[Product Type] = {'F'}

Extension

)

....

That is just one way.......

Hope it helps.

Adding calculated fields to Pivot Table

Mon, 19 Dec 2011 21:10:36 GMT

Sam,

Thanks for the feedback, but I would like to get the variance of "F" between the month 1 and month 2, and of "R" between month 1 and month 2 not the difference betweet F and R.

Adding calculated fields to Pivot Table

Mon, 19 Dec 2011 21:53:56 GMT

So, you want R vs. prior month, F vs. prior month?

There are a few ways, but let's take a direct approach.

Modify your load script such that you have, on every row, a [Prior Month]

That is done via addmonths([Month],-1) AS [Prior Month]

Then, to get the prior month value of F, in your expression, use something like this:

=sum

(

[Product Type] = {'F'},

Month = [Prior Month]

Extension

)