Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Totals in Excel

Hi

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

ProjectJanfebmarch
Project 1100
Project 2100
Project 3300
Project 4100

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

7 Replies
rubenmarin

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:

Not applicable
Author

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

rubenmarin

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

rubenmarin

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.

Not applicable
Author

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

Many Thanks

Not applicable
Author

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

Not applicable
Author

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