topic Re: Beginner Qlikview and graph/chart designing in QlikView

Beginner Qlikview and graph/chart designing

Mon, 21 May 2012 14:46:34 GMT

I'm learning how to create several charts & graphs using the attached data?

I want to take weather temperature data for two areas, annually to hourly: from 2010 (365 days - column and 24 hours for each day in rows)

These are the graphs I would like to make:

1. show a date with temps for 24 hours

2. show a dates and hours with average high and/or low temps

3. show hourly,daily,weekly,monthly, annually high and/or low temps

4. show the date(s) when a specific temp range occured

Make sense? The attached excel spreadsheet has a sample chart tab... Creating this must be super easy...

Re: Beginner Qlikview and graph/chart designing

oscar_ortiz — Mon, 21 May 2012 16:39:18 GMT

I would start with something simple like this and then gradually work my way towards something more complex.

I've added date related fields such as Year, Month and Week to the data model to make it easier to select.

Good Luck

Oscar

Beginner Qlikview and graph/chart designing

Mon, 21 May 2012 17:05:51 GMT

Oscar: Thank you for responding, but I'm unable to view. I'm using the starter free personal editon version and I can not share (send or recieve) .qvw files.

Message, "Must update key.." and then "No data to display".

I can see the script... Not sure how to write script.... How can I view your attachment or graph?

How can I show hourly temps / hourly data?

Charlie

Re: Beginner Qlikview and graph/chart designing

oscar_ortiz — Mon, 21 May 2012 17:47:21 GMT

The following snippet will create the script you need to build the data model.

Once the data model is created you will see that the data model has two tables SummaryData and DetailData.

As far as the charts themselves go, dimensions are using one of the date fields plus the location. The expressions are pretty easy as well with Sum(HIGH) or Sum(Temp) being used.

On the second chart I've also added a check to limit the user to select a single date. That's on the General tab.

Hourly chart would be something liek this:


SummaryData:
LOAD
          'South-' & Date as Key,
          Week(Date) as Week,
          Month(Date) as Month,
          Year(Date) as Year,
          Name, 
          TOTAL, 
          AVERAGE, 
          LOW, 
          HIGH
FROM
[Downloads\2010 North and South Weather data.xlsx]
(ooxml, embedded labels, table is year10)
;
LOAD 
          'North-' & Date as Key,
          Week(Date1) as Week,
          Month(Date1) as Month,
          Year(Date1) as Year,
          Name1 as Name,
          TOTAL1 as TOTAL, 
          AVERAGE1 as AVERAGE, 
          LOW1 as LOW, 
          HIGH1 as HIGH
FROM
[Downloads\2010 North and South Weather data.xlsx]
(ooxml, embedded labels, table is year10);
TempData:
LOAD 
          'South-' & Date as Key,
          'South' as Location,
          Date,
          [1], 
          [2], 
          [3], 
          [4], 
          [5], 
          [6], 
          [7], 
          [8], 
          [9], 
          [10], 
          [11], 
          [12], 
          [13], 
          [14], 
          [15], 
          [16], 
          [17], 
          [18], 
          [19], 
          [20], 
          [21], 
          [22], 
          [23], 
          [24], 
          [25]
FROM
[Downloads\2010 North and South Weather data.xlsx]
(ooxml, embedded labels, table is year10);
DetailData:
LOAD 
          'North-' & Date as Key,
          'North' as Location,
          Date,
          [110] as [1],
          [26] as [2], 
          [31] as [3], 
          [41] as [4], 
          [51] as [5], 
          [61] as [6], 
          [71] as [7], 
          [81] as [8], 
          [91] as [9], 
          [101] as [10], 
          [111] as [11], 
          [121] as [12],
          [131] as [13], 
          [141] as [14],  
          [151] as [15], 
          [161] as [16], 
          [171] as [17], 
          [181] as [18], 
          [191] as [19], 
          [201] as [20], 
          [211] as [21], 
          [221] as [22], 
          [231] as [23], 
          [241] as [24], 
          [251] as [25]
FROM
[Downloads\2010 North and South Weather data.xlsx]
(ooxml, embedded labels, table is year10);
Data:
CrossTable(Hour, Temp, 3)
LOAD *
Resident TempData
;
DROP Table TempData;

Re: Beginner Qlikview and graph/chart designing

Mon, 21 May 2012 20:11:51 GMT

I'm havnig issues getting started. How do I import the data?

I see the charts with no data to display. I think I need step-by-step instructions to get me started...

Re: Beginner Qlikview and graph/chart designing

oscar_ortiz — Mon, 21 May 2012 23:24:46 GMT

Charlie,

Ok, sorry didn't realize that you are that new to QlikView. I'll try to do my best explaining how to add a spreadsheet to your QlikView document.

Open up your QlikView document and go to the script editor (from the drop down menu choose File then Edit Script, or you can use Ctrl+E).

In the script editor there position your cursor to your last empty line Line 12 for example if you're starting from scratch.

Select the Table Files wizard as seen below and use it to select where your spreadsheet exists. Select your spreadsheet and check for the following:

If your spreadsheet contains multiple tabs you may need to click on the Tables drop down to select the table containing the data you are trying to read.

If your table contains embedded labels in the columns you may need to click on the Labels drop down and select "Embedded Labels". If you select none QlikView will name those columns for you as A, B, C, etc. You can always rename as necessary, either in the wizard or in the script itself.

I like to use the wizard to start out then modify using the script editor later. I really use the script editor to help with the directory structure of where the file that you're loading exists.

If you click finish at this point your script editor should remain open and you will see the script that the wizard has created for you. Now this is where you can get creative using QlikView's functions to help with your needs.

In my previous post I included the script that I used to create my data model. The only thing that you may have to change is the location where the spreadsheet exists.

For example I had:

FROM

[Downloads\2010 North and South Weather data.xlsx]

(ooxml, embedded labels, table is year10);

You might have something like this:

FROM

(ooxml, embedded labels, table is year10);

You should simply be able to modify my script to fit your location.

Once you have your script ready to go, save your document and reload it. You can again use the drop down menu File then Reload or you can use the shortcut keys Ctrl+R or the Reload Icon. You'll come to see that there are many ways of accomplishing the same thing. It's going to be whatever is more comfortable for you.

Once you've gotten to the point of the data being loaded into your QlikView document you should now be able to create the charts that you need. Again if you have my script already it should just be a matter of reloading your document.

Re: Beginner Qlikview and graph/chart designing

Tue, 22 May 2012 01:33:22 GMT

OK. The 2 graphs are:

1. 2010 High Temps (the date are not lining up)

2. High Temps for Day (the display say: "Calculation condition unfilled")

Also, how are these graphs created?

1. show a dates and hours with average high and/or low temps

2. show hourly,daily,weekly,monthly, annually high and/or low temps

3. show the date(s) when a specific temp range occured

Re: Beginner Qlikview and graph/chart designing

Tue, 22 May 2012 02:18:20 GMT

Hi check this out. This will walk you through the most basic stuff.

http://www.qlik.com/us/services/training/free-training

Re: Beginner Qlikview and graph/chart designing

Tue, 22 May 2012 14:30:49 GMT

I am able to create the basic line charts, except for the "week". Not sure why this is not working, "no data to display". I think it's the expression definition:

Sum (HIGH)& Min(Date) & '-' & Max(Date)

How can I create a gauge chart, pie chart, and others like a thermometer with temps?

Re: Beginner Qlikview and graph/chart designing

oscar_ortiz — Tue, 22 May 2012 14:52:00 GMT

What I'm doing with the Week chart is limiting what the user can see by insisting that a single week is selected.

So if a user chooses a single week then I will show my data.

This is accomplished using a condtition on the General tab of the chart properties.

So I am checking for a single week being selected by using the "Count( DISTINCT Weel) = 1" for the Calculation Condition.

Simple dimensions of Date and Location

And finally a simple expression of Sum(HIGH) to return the high temperature.

I have an expression in my Label to make my title dynamic. So depending upon the week you select I will show the first day of the week MIN and the last day of the week MAX in the title.

Your next question is in regards to guages. The trick with gauges is that they do not require dimensions. Only an expression. Generally we use guages to reflect some sort of ratio. There is so much you can do with gauges, I would suggest looking at some of the examples that are part of the install.