topic Help with Line Charts in QlikView

Help with Line Charts

Thu, 21 Jan 2010 14:43:15 GMT

Hello !

Sorry for such a silly question but I need to build a graph from the situation below:

Say that I have a database with STOCK NAME, DATE and PRICE.

I dont know how many different STOCK NAMEs there are on the database.

I'd like to build a graph tracking on the Y-Axis the value, on X-Axis the dates and, for each STOCK NAME there would be a different line .

Would U help me ?

Thanks !

ps : there's a tiny QVW that I hope can be helpful for you !

Help with Line Charts

hector — Thu, 21 Jan 2010 14:56:52 GMT

Hi, see the attached document, and tell me if it works for u

RGDS

Help with Line Charts

Thu, 21 Jan 2010 15:40:42 GMT

Perfect !

Thank U so much !

Now do you know how to make stock prices really go UP ? I mean on the stock market itself ?

See ya !

Help with Line Charts

hector — Thu, 21 Jan 2010 15:53:17 GMT

I don't understand what you want.... are you talking about this setting? Axes->Uncheck Forced 0???

rgds

Help with Line Charts

Thu, 21 Jan 2010 15:56:08 GMT

Sorry I'm just kidding about the stock market.

No one knows how to make it go up.

Anyway, forget about this.

Your answer was perfect.

Help with Line Charts

hector — Thu, 21 Jan 2010 16:03:17 GMT

LOL!!!! xD

Rgds

Help with Line Charts

Fri, 15 Oct 2010 22:51:53 GMT

Hector, I appreciate you providing such a fantastic answer. Unfortunately I can't see it!

I am trying to load the file you provided into the free download version but I get an error message "This file contains inconsistencies and cannot be opened by Personal Edition."

Do you know what this "problem" might be and/or is there a way for me to get around it?

Otherwise, is there some way you could "describe" what the essential part is in order to make sure that a line chart has "multiple" lines, one for each stock symbol. My application has a minimum and maximum number for each symbol so I would really like to lines (or use the min/max feature) but I somehow can't figure out what the magic incantation is in order to get each of the symbols to have their own line in the line chart. When I select more than one symbol it creates a line which is an amalgamation of each of the selected items.

Thanks in advance for any consideration,

Gary

Help with Line Charts

johnw — Fri, 15 Oct 2010 23:13:26 GMT

You cannot download other peoples files to personal edition. You can only use your own files, and files that QlikTech themselves have unprotected to allow access.

The first chart has two dimensions, date and stock name. That gives you one line per stock name.

The second chart has only one dimension, date. It then uses one EXPRESSION for each stock of interest, using a sum(if()) to generate the result for that one stock name.

Help with Line Charts

Sat, 16 Oct 2010 02:56:37 GMT

John,

Many thanks for the quick response! I suspected the problem was that I'm not allowed to download files into the personal edition however that was not exactly the error message I was getting. I have tried downloading some other files and do indeed get the message which tells you "if you are running on another computer" kind of thing and that you will essentially lose all of your other files. However, for this particular file, it immediately went to the error message I stated and didn't ever offer me an option to use up one of my 4 chances. (I'm documenting this so you know I am aware of the limitations but this particular file seemed to be "different" than the limitations. 🙂

I believe I am understanding your answer some (and appreciate you helping a newbie). I have tried adding the "Stock name" as an additional dimension because I suspected I needed to have that but, so far, I haven't gotten any more lines.

I am slightly confused by your answer though.

By the way, I am only using "stocks" as an example. That's not the kind of data I'm storing but in my mind the concept is the same so I'm using this analogy so I can figure out what to do without having to over-describe my data. I must admit I'm a bit surprised to not see any example applications which have something to do with tracking historical stock market data for multiple stocks.

Your last paragraph above says "second chart" - by that do you mean the "expressions" tab or a separate graph? Perhaps you're talking about a "combo" chart?

For each "stock" there is a max and min value value associated with a date. I understand putting the date as the Dimension and I am currently putting max and min as the expressions. That only give me 2 lines. I don't understand how to make the "y value" be broken apart into a separate set of max and min for each stock name. As I understand your final paragraph, it would seem that I need to create an "expression" for each stock. I was hoping to choose the names from all of the available ones in the "side boxes" of data and not have to go to the trouble of placing the individual names in the chart. (At least that's how I'm interpreting what you say.) Yes, I realize that the "all" option which would come up first would be very messy, but I can put up with that until I select a few values. 😉

Help with Line Charts

johnw — Mon, 18 Oct 2010 18:27:29 GMT

Here's the script.

TEST:
LOAD * INLINE [
STOCK_NAME ;DATE ; PRICE
PETR4 ;02/01/2010 ; 200
PETR4 ;03/01/2010 ; 210
USIM5 ;02/01/2010 ; 30
USIM5 ;03/01/2010 ; 40
] (DELIMITER IS ';');

There are TWO separate charts in the example. The first chart is built like this:

Dimension 1: DATE
Dimension 2: STOCK_NAME
Expression: Sum(PRICE)

The second chart is built like this:

Dimension: DATE
Expression 1: Sum (if(STOCK_NAME='PETR4',PRICE))
Expression 2: Sum (if(STOCK_NAME='USIM5',PRICE))

I'm not suggesting that you do it like the second chart. I'd strongly suggest that you NOT do it that way. But that's what was in the posted example.

Now, it sounds like you're trying to have a graph with TWO lines for every stock, associated with the max and min values for the day? That's where we get into trouble. Basically, you can have EITHER a second dimension OR a second expression, but not both. QlikView is basically telling you that your chart will be too busy, and that you won't be able to distinguish between the min and max lines for each stock. You can fake it, but I don't know of an easy way.

How to do it exactly depends on exactly how your data is stored. And since this isn't your real example, I'm not sure how much detail we should get into with it. But let's say you have a table like the one above, but with a timestamp for every stock sale, so you have every price. The timestamp is then parsed to give you a DATE field, but you'll have many records for each date. You can build a new table like this:

MinMax:
LOAD
STOCK_NAME
,STOCK_NAME & ' ' & subfield('min,max',',') as STOCK_NAME_MIN_MAX
;
LOAD fieldvalue('STOCK_NAME',iterno()) as STOCK_NAME
AUTOGENERATE 1
WHILE len(fieldvalue('STOCK_NAME',iterno()))
;

Which will have a table of all stock names with both 'min' and 'max' after them. Then build a chart like this:

Dimension 1: DATE
Dimension 2: STOCK_NAME_MIN_MAX
Expression: if(right(STOCK_NAME_MIN_MAX,3)='min',min(PRICE),max(PRICE))

There are probably better answers. That's just the first thing that occurred to me.

Help with Line Charts

Tue, 19 Oct 2010 01:12:01 GMT

I am understanding this more and more and appreciate the wonderful feedback.

I have played around a little with this example and found several interesting things which are either helping or confusing me more, sometimes I can't tell which. 🙂

First, I added a little more data:

TEST:
LOAD * INLINE [
STOCK_NAME ;DATE ; PRICE
PETR4 ;02/01/2010 ; 200
PETR4 ;03/01/2010 ; 210
PETR4 ;04/01/2010 ; 220
PETR4 ;04/25/2010 ; 230
USIM5 ;02/01/2010 ; 30
USIM5 ;03/01/2010 ; 40
USIM5 ;04/01/2010 ; 50
USIM5 ;04/15/2010 ; 60
] (DELIMITER IS ';');

A- When I plotted this using the line chart it did indeed come out the way I expected. As you may note one of the things I did is change the dates a little (the last date is different for the 2 stocks) and I got the appropriate "shorter" line on the one that ended sooner. So far, that's good because it's what I expected.

B- I then made another change. I changed the expression from sum(PRICE) to simply say PRICE. When I did this there appears to be no difference in the chart. Therefore, it makes me wonder why the sum() calculation is needed at all?

C- I then removed the 2nd dimension (STOCK_NAME) but the resulting graph was very peculiar: it seemed to "throw away" almost all of the data and only seemed to pick 2 data points to display. I have no idea why that looks that way.

D- I then changed the expression back to sum(PRICE) and "all of the points" seemed to come back but this time they are (I believe understandably) now "merged together" (i.e. summed) and then plotted. This is the "problem" I believe I am having with my data (data merged together) even when I select a 2nd dimension. This makes me believe that perhaps the 2nd dimension I am choosing isn't right. I have tried selecting several of the other values which might be appropriate but none of them seem to have any effect at all; the graph has no change after I add the dimension and click "Apply" to see it. Let me modify that slightly -- I am not getting what I want when I select the specific item (i.e. the name) as the 2nd dimension, however I have been able to get multiple lines when I select certain other columns of the data.

E- I was getting the "multiple lines" for some of the fields (I haven't figured out the pattern) when I had only one expression (the minimum value.) When I then added in the expression for the maximum value "suddenly" the legend on the top right corner changes from the 2nd item mentioned in the dimensions into the sum(min) and sum(max) and the 2nd dimension item simply "disappears."

All of the above is for the "1st chart" both because it seems to be simpler (and it seems as though I need to keep things simple right now) and also because I do not want to have to "call out" each individual name in the expression field because there isn't any way I am going to know what they are in a production environment.

At this point I guess my understanding would be helped if I understood why in #E above adding the 2nd expression seems to get rid of the 2nd dimension.

If you like I believe I could probably upload an XL file of a chunk of my data because I have a "table chart" which shows this subset of information I'm interested in. (This gets rid of the problem of having a lot of other extraneous columns and data.) As I look at this some more perhaps it may be necessary for me to create a "virtual field" (which is the impression I get from your 2nd example) which combines the timestamp and the value but my head still doesn't understand how that would make it possible to get my desired results. Perhaps if I understood why #C above behaves the way it does I might understand better how to get the data into the chart correctly.

I am confident QlikView is going to be able to display the chart I want once I figure out how to specify it appropriately. I continue to be surprised that none of the examples I have found so far show stock market data from multiple stocks over a period of time similar to what the 1st example chart showed.

Help with Line Charts

johnw — Tue, 19 Oct 2010 17:32:46 GMT

B - The sum() is NOT needed at all in this particular chart because there is only a single value of PRICE for any combination of the given dimensions. I'd probably personally use only(PRICE) which will also return the same results. I think the reason I do it is based on SQL concepts. The dimensions of the chart are your group by. SQL requires an aggregation function if you're doing a group by. So I use an aggregation function, even though QlikView doesn't require one in this case. Feel free to not do so, as I can't think of any QlikView-centric reason for using only() here.

C & D - Here, a sum() or something else IS needed. Your PRICE is no longer unique based on the date dimension. So for 02/01/2010, you have a price of 200 and a price of 30. What should it display? It doesn't know, so it displays nothing. Only two data points have a unique price for the date, so only those two points display.

E - As I said earlier, you can have a second dimension or a second expression, but not both. Well, technically you can have both, but it won't give you lines for both. It appears to ignore your second dimension in that case. Why? Well, honestly, I wouldn't have designed it that way, so I can't really say why. I'd probably have moved the second dimension to the X axis, giving you a separate line chart for each, though I don't think that's what you personally want. The other reasonable alternative in my mind would be to automatically concatenate the second dimension and the expression name, and so overlap all of the lines instead of giving you "separate" line charts. I think that's what you're looking for. QlikTech apparently chose to do nothing with it instead. Consider it an unfortunate limitation, then.

I see your Excel file, but I'm not sure what you want your resulting chart to look like, so it's only half of being able to give you a solution. Can you mock it up or otherwise describe it?

Help with Line Charts

Tue, 19 Oct 2010 23:31:07 GMT

Oh! Oh! Oh! You hit the nail on the head. The key phrase I did not understand was "you can have a second dimension or a second expression, but not both." It also helped a lot to know that, once QlikView couldn't "use" a dimension it was simply ignoring it. Perhaps a "message" of some kind would be appropriate so you'd know it's not helping...

When I removed the 2nd expression I got the chart I was looking for! I am attaching a picture of what I got (after adding in a few more TTagID items.) It's giving exactly what I want to see - the "trend" of each item following a "pattern" but some much higher or lower than others.

I'm next going to see about using a "stock" like chart (I realize I'll have to convert to Combo to do that, so it's not trivial but I'll go do it shortly) and try using the max/min feature there to get both values on one line for each tag. Then again, seeing how "crowded" it can get with only a few tags, maybe separate min and max charts makes more sense. (Pretty easy to do also, get the chart all set up for max and then clone it and change the expression to min - cool!)

Thanks also for the insight on sum() and your use of it. I understand perfectly being "used to" a certain way of doing things and keeping that up even if you don't have to.

As to the C&D comment "It doesn't know, so it displays nothing" that's interesting too. Maybe it's simply my newness to this, but simply "ignoring" a problem like this doesn't help me learn what to do to fix the problem. I get "errors" in Visual Studio many times although it plows on ahead and gives me a report. When it doesn't look right, I go to the list of error messages first to get an idea what might be wrong. (It's not always the problem I'm looking for, but it is a problem...) I guess QlikView doesn't have a way to display "errors" like this. Perhaps I'll get used to it...

Thanks again for the great advice. I appreciate the time and effort and hope to get good enough to help contribute to the community when I get a little better.

Bottom line for this problem: "you can have a second dimension or a second expression, but not both"

Help with Line Charts

johnw — Wed, 20 Oct 2010 00:17:42 GMT


GSaxer01 wrote:As to the C&D comment "It doesn't know, so it displays nothing" that's interesting too. Maybe it's simply my newness to this, but simply "ignoring" a problem like this doesn't help me learn what to do to fix the problem. I get "errors" in Visual Studio many times although it plows on ahead and gives me a report. When it doesn't look right, I go to the list of error messages first to get an idea what might be wrong. (It's not always the problem I'm looking for, but it is a problem...) I guess QlikView doesn't have a way to display "errors" like this. Perhaps I'll get used to it...

To me, it's a consequence of QlikView being "looser" than SQL. SQL would REQUIRE that you specify an aggregation function. It requires you to say what to do with multiple values if they're possible. QlikView doesn't require any such thing; it just returns null(). I'm sure it could return an error, as you'll sometimes see error messages instead of your chart, so QlikTech probably decided that this wasn't an error, and that null() was the correct response. I don't really agree, but I'm used to it. As with all new "programming languages", you get used to the quirks eventually.