topic Re: want to build graph using dashboard from excel in QlikView

want to build graph using dashboard from excel

Thu, 31 Oct 2013 14:58:37 GMT

I have a data in an excel which has 1st three columns as project1, sub project and sub-sub project, and the fourth column is KPI metrics and the 5th column is target and the 6th column onwards are months and the data in each of the columns below.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
Project	Sub-Project	Sub-Sub-project	KPI	Target	Aug
A&A	A&A (Adam)	A&A (Adam - Aple)	Answerd	90%	93%
			Abnd	<5%	4.1%
			FCR	90%	92%

Want to show a graphical presentation of the above data with the multibox showing 'Project', 'Sub-Project'and 'Sub-Sub-project' and the values of all the KPI's show for Aug shown in the Bar graph.

Added a dummy data for reference herewith.

Re: want to build graph using dashboard from excel

Gysbert_Wassenaar — Thu, 31 Oct 2013 15:30:39 GMT

Create a load statement for the excel file using the file wizard. You can choose to fill the x-project fields with values from the row above if the cell is empty. And you can enable the CrossTable option so all the Month columns are transformed to a usable form with the Months in a Month field and the KPI values in a Value field. You can then use Month as dimension in the bar chart and select Aug if you want to see only the KPI's of Aug.

Feel free to post the excel file if you aren't able to get the result you want.

Re: want to build graph using dashboard from excel

Thu, 31 Oct 2013 15:51:22 GMT

Hi,

Try the hierarchy function.

Rebeca

Re: want to build graph using dashboard from excel

Thu, 31 Oct 2013 16:03:21 GMT

You'll have the example at hand for review

Re: want to build graph using dashboard from excel

Tue, 26 Nov 2013 06:47:18 GMT

Hi Gysbert, we had tried this function earlier but didnt seem to work, anyways as suggested have attached a sample dummy data.

Re: want to build graph using dashboard from excel

albertovarela — Tue, 26 Nov 2013 07:07:01 GMT

Hello, Following your requirement this is how I would do it:

Table:

CrossTable(Month, KPI,4)

LOAD

[Service Desk],

Vertical,

[SLA's of Project],

[SLA Target],

May,

June,

July,

Aug

FROM

\\YourFile.xlsx

(ooxml, embedded labels, table is Sheet1);

Sample app is attached

Re: want to build graph using dashboard from excel

Tue, 26 Nov 2013 08:22:35 GMT

Hi Alberto,

It worked, but we also have some conditions and validations

1) I am looking at the change of the color of the graph based on some thresholds for eg. if the Actual is <5% of the SLA Target then the bar graph should be Amber in color, if the Acutal if <10% of the SLA Target the barg graph should be in Red color.

2) Also this will be a continuous data, so we might have month wise columns being added after 'Aug' so how do we populate that. I guess you are using the value '4' in the crosstable function.

Re: Re: want to build graph using dashboard from excel

albertovarela — Tue, 26 Nov 2013 17:29:52 GMT

Hi,

For 1) Try something like this on the background colour for the expression

For 2) The 4 is identifying the number of fields preceding the table to be transformed, in other words that is your prj, sub-proj, sub-sub-proj and target. Just keep adding rows for the month in your load statement and it will work fine.