topic Matching an Existing Excel report in QlikView

Matching an Existing Excel report

Mon, 09 Jan 2012 14:45:33 GMT

I have a mysql database which has actual & budget figures for each week for specific KPI's.

I want to try to replicate an existing excel spreadsheet report where the KPI's are listed vertically and the actual & budget figures are verticaly along side them.

KPI Actual Budget Variance

1 100 110 10

2 50 100 50

and then colour code the variance etc.

Is this possible?

I want to do this as a starting point because the users are used to this format, and then craete a new Dashboard report with the same data to show them what can be done.

thanks in advance for any help

Dave

Matching an Existing Excel report

swuehl — Mon, 09 Jan 2012 15:58:18 GMT

Yes, I think this should be possible.

You KPI could be a dimension and Actual, Budget aggregation expression. Variance would then just be calculated as variance of those latter two expressions. The style you requested could already be supported by a straight table, or maybe with a pivot table.

Colour coding could be achieved using the "Visual Cues" tab of chart properties or using a colour expression for an expression attribute (available in expression tab, click on plus sign next to expression).

If you post a small subset of your data, I guess someone here might be able to help you more with the object creation, formatting and expressions.

Regards,

Stefan

Re: Matching an Existing Excel report

Mon, 09 Jan 2012 16:29:34 GMT

Thanks Stefan,

I'm not sure exactly what objects I need to use, I was looking for a pivot table initially but I guess its hidden inside another one.

I'm trying to uploaded a excel spreadsheet with the actual data in it, not included the budget figures, but they follow the same format, and a copy of the report I'm trying to match, or get close to at least

Re: Matching an Existing Excel report

swuehl — Mon, 09 Jan 2012 16:36:29 GMT

The pivot table and other charts can be created using (context menu on sheet): New Sheet Object --> Chart, then select the chart type.

Maybe I have time to look into your data tomorrow, or someone else might help you creating your table.

Re: Matching an Existing Excel report

Mon, 09 Jan 2012 16:48:33 GMT

many thanks.

I'll have a play around

Re: Matching an Existing Excel report

Tue, 10 Jan 2012 13:39:14 GMT

Hi again,

I've had a play and I cannot find any way of creating a pivot table.

The only options I get from the 'new sheet object' list is a 'Table Box' and I can't see anywhere in it that allows me to do a pivot on the data to make column headings row headings.

can you help please

thanks

Dave

Re: Matching an Existing Excel report

swuehl — Tue, 10 Jan 2012 13:50:56 GMT

In the context menu for 'new sheet object' (or by using the menu bar), you don't see an entry for 'chart'?

If you select chart, on general tab, there are 13 different chart objects to select from, one of them is pivot table.