topic Re: How to you stop a horizontal chart from exporting to Excel horizontally? in QlikView

How to you stop a horizontal chart from exporting to Excel horizontally?

Mon, 03 Mar 2014 03:35:15 GMT

I have created a table chart to approximate a Profit & Loss Statement.

The dimension is financial periods, and to represent this dimension as columns with the expressions running in rows down the page,

I had to tick the Horizontal box on the Presentation tab in Properties.

Unfortunately, when the chart exports to excel, the periods return to rows, and the expressions become columns.

Any idea on how to force QV to export it as per the chart object displays?

Cheers

Re: How to you stop a horizontal chart from exporting to Excel horizontally?

sebastiandperei — Mon, 03 Mar 2014 03:49:35 GMT

Hi.

Use a Pivot Table. Drag and drop one expression label to the left (until the blue flag is vertical), then drag and drop the Month Dimmention to the right (until the flag is horizontal)

Re: How to you stop a horizontal chart from exporting to Excel horizontally?

Mon, 03 Mar 2014 04:03:55 GMT

Thanks for that...it works, but I lose most of my formatting, and the the columns must now all be the same width - which is a problem because the chart expression names are quite long, but the data points are short i.e. I end up only showing 3-5 data columns.

Any other ideas?

Re: How to you stop a horizontal chart from exporting to Excel horizontally?

aveeeeeee7en — Mon, 03 Mar 2014 04:22:13 GMT

If you want to force QV to export it as per the chart object displays than

you can simply right click on the chart > Click on Copy to Clipboard > Full Table > Open Excel sheet and paste the copied Table. After doing this you can do table formatting from excel.

Else, Use a Macro to Export Excel.

Regards

Aviral Nag

Re: How to you stop a horizontal chart from exporting to Excel horizontally?

sebastiandperei — Mon, 03 Mar 2014 05:03:04 GMT

It's not a good new...

The only way to do that is making a table (from excel, or from inline) that has one column with the titles, and other with the expressions. Like:

concept_fld expression_fld order_fld

Sales income sum (sales) 1

Budget sum (Budget) 2

....

So, in your script, next to the reading of this table, you should set the variable:

expressions: Load concept_fld, expression_fld, order_fld From expressions.xls;

aux: Load

'Peek(order_fld,'&Concat(expression_fld, ',', order_fld) &')' as aux_exp

Resident expressions;

LetvExpression = Peek('aux_exp');

Drop table aux;

Now, your table will get two dimmentions: Period_field and expression_fld. Order the second by order_fld, and your expression will be =$(vExpression)

Like this way, you can use the excel file to standarize the expressions for the application.