topic Re: Totals in Excel in Connectivity & Data Prep

Totals in Excel

Thu, 29 Jan 2015 15:55:43 GMT

I'm new to Qlik so this may be a simple question, but I'm struggling to find a solution to a graph problem

We have an excel placed out in the format below

Project	Jan	feb	march
Project 1	100
Project 2	100
Project 3		300
Project 4			100

I would like to produce a graph in Qlik that shows a monthly running total, months along the bottom with the total values in a graph (basically turnover each month)

So Jan would show 200

Feb 300

March 100

Is this possible to do?

Many Thanks

Re: Totals in Excel

rubenmarin — Thu, 29 Jan 2015 16:18:09 GMT

Hi Michael, you can drag and drop excel in QV, this will open the File Assistant

Press Next 2 times and press the 'Cross table...' button

Set a name for the attribute field and another for the data:

From the you can create a bar chart with the attribute field (Month in my case) and an expression like:

Sum(DataField) (Value in my case)

In the 'Sort' tab you can check load order for month:

this way you can get this graph:

Re: Totals in Excel

Thu, 29 Jan 2015 16:24:18 GMT

HI Thanks for the quick response,

Again could be a daft question I'm using Qlik Sense Desktop at the moment does this still apply?

Many Thanks

Re: Totals in Excel

rubenmarin — Thu, 29 Jan 2015 16:38:38 GMT

Again I didn't saw this was a Sense question, sorry.

It can work but I think the assistant can't help you with crosstable, you can avoid the assistant and edit the script directly, you can use:

CrossTable(Month, Value)

LOAD Project,

Jan,

feb,

march

FROM

[ExcelPath\ExcelName.xls]

(biff, embedded labels, table is Hoja1$);

Hope this helps

Re: Totals in Excel

rubenmarin — Thu, 29 Jan 2015 16:46:00 GMT

For Sense, inside the editor you need to create a file connection to the folder where excel is stored, if you name this 'Data', you can use an script like this:

For sorting month by load order you need to uncheck the auto-sort, and in custom sort unckeck all.

Re: Totals in Excel

Thu, 29 Jan 2015 21:39:55 GMT

Thanks for that, I think I get what I need to do now.

Many Thanks

Re: Totals in Excel

Mon, 02 Feb 2015 21:00:10 GMT

HI,

I have been able to get the information to display in Qlik Desktop using the formula above.

I have also added a table to show all the projects and all the months, to do this I had to add the load command again without the cross table reference.

However if I select a month using the chart produced above using the crosstable values, the table below does not show the project with values in that month (if that makes since).

Many Thanks

Re: Totals in Excel

Mon, 02 Feb 2015 21:23:23 GMT

Sorry all sorted, I'd removed project from the crosstable load. Replace and all working