How do I insert a calculated row in a straight table ?

[#1]

Hi Experts,

I have a straight table which consists of a number of columns (January to December) and 4 rows. The month columns are expressions using set analysis.

The rows are derived from two dimensions called 'Business' and 'Status'. There are 2 possible values for Business: Renewal and New Business. And there are 2 possible values for Status: Budget and Forecast

See below...

I would like to insert a calculated row in between the 2nd and 3rd rows called 'Renewal Variance' which will calculate the difference between the Budget and Forecast amounts for the two Renewal rows above it.

Similarly I would like to insert a calculated row after the 4th row called 'New Business Variance' which will calculate the difference between the Budget and Forecast amounts for New Business.

Is this possible in QlikView ?

MV

[#2]

I did similar kind of work see the below example: table_Actual: LOAD      Status,     Jan,     Feb    FROM (ooxml, embedded labels, table is Sheet1); load 'Variance' as Status, (Max(Jan)-Min(Jan)) as [Jan], (Max(Feb)-Min(Feb)) as [Feb] Resident table_Actual; in the above example I used max and min but in your query max/min would not work. You might use some other function.

[#3]

I don't understand what it is your code is doing. Can you break it down for me, line by line, please ? I'm not an experienced QV developer by a long chalk.

[robert99 · Accepted solution]

I don't think there is a simple way to do this

But try this. It might be what you want

How to Create a Profit and Loss Statement in QlikView

Jennell McIntire

How to Create a Profit and Loss Statement in QlikView

EDIT. The way I would do this is have the month on the vertical (as a straight table)

And 6 column heading as follows

Renewal budget

Renewal Forecast

Renewal Variance

etc

This gives the information but in a different format

Then you could try properties --> presentation --> horizontal. (to change months to columns again

[simondachstr]

Start by using a pivot table instead of having single expressions for each month.

[#6]

That's a great solution. It worked a treat. The Month became the Dimension, and I created 6 expressions as you suggested. Then I flipped the whole thing horizontally. Didn't know you could do that.

Many thanks

MV