Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

want to build graph using dashboard from excel

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 1Header 2Header 3Header 4Header 5Header 6
ProjectSub-ProjectSub-Sub-projectKPITargetAug
A&AA&A (Adam)A&A (Adam - Aple)Answerd90%93%
Abnd<5%4.1%
FCR90%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.

Tags (3)
7 Replies
MVP & Luminary
MVP & Luminary

Re: want to build graph using dashboard from excel

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.


talk is cheap, supply exceeds demand
Not applicable

Re: want to build graph using dashboard from excel

Hi,

Try the hierarchy function.

Rebeca

Not applicable

Re: want to build graph using dashboard from excel

You'll have the example at hand for review

Not applicable

Re: want to build graph using dashboard from excel

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

Partner
Partner

Re: want to build graph using dashboard from excel

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

Not applicable

Re: want to build graph using dashboard from excel

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.

Partner
Partner

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

Hi,

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

Capture.JPG.jpg

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.