topic Re: How do I insert a calculated row in a straight table ? in QlikView

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

Tue, 30 Sep 2014 13:50:20 GMT

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 ?

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

Tue, 30 Sep 2014 19:51:10 GMT

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.

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

Wed, 01 Oct 2014 07:42:06 GMT

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.

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

robert99 — Wed, 01 Oct 2014 10:29:34 GMT

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

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

simondachstr — Wed, 01 Oct 2014 10:38:45 GMT

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

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

Wed, 01 Oct 2014 12:28:27 GMT

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