topic Re: Line chart, 2 dimensions, 2 expressions in QlikView

Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 13:11:49 GMT

Hi,

I'm a QlikView newbie and I've been trying to solve a problem...without any luck.

I have a line chart with 2 expressions showing respectively the expected amount of rainfall per day, and the real (measured) amount of rainfall per day.

I can correctly see just one of the two expressions at a time depending on which dimension I use.

If I use dimension called "date_expected", I can correctly see the line chart with expression = "expected_values"

If I use dimension called "date_measured", I can correctly see the line chart with expression = ''measured_values"

If I use both dimenions, on the graph one line is correct but the other is not.

In the attached image I used both dimensions (date_expected on top), as you can see from the last chart the measured_values are not correctly shown.

What I want is to (simply...) merge the first 2 graphs in one chart

Thanks in advance

Michel

Re: Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 13:20:32 GMT

You would need to use a calendar with either a new table for measured with the dates linked to a common date,

or the slower option of leaving the calendar unlinked and using an if statement like

Measured:

sum(if(date_measured=calendardate,measuredamount))

Line chart, 2 dimensions, 2 expressions

danielrozental — Thu, 03 May 2012 13:21:32 GMT

Michel, simplest way to do this, just have a single date field on the model.

Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 14:40:44 GMT

I give you more in details

I fetch the data (expected and measured) from 2 different databases.

The column "data_measured" looks like this:

12.04.2012 09:15

12.04.2012 09:30

12.04.2012 09:45

12.04.2012 10:00...

Every 15 minutes there is a new measure. I group together these measures in two groups: amount of rain from midnight till noon and amount of rain from noon till midnight (for each day)

The column "data_expected" looks like this:

12.04.2012 00:00

12.04.2012 12:00

13.04.2012 00:00

13.04.2012 12:00...

Here we have 2 measures per day (each measure matches the two time slot mentioned above, midnight-noon, noon-midnight), so e.g. 12.04.2012 00:00 refers to the amount of rain between midnight and noon.

If I use the column "date" of measured_values as dimension for the expected_values I don't get a correct chart.

Re: Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 15:55:22 GMT

Can you please explain me better, what do you mean for "common date"?

Common date is a copy of "date_measured"?

The calendardate should be linked to "date_measured""?

I added some more explanation above about dates.

Thanks for the help!

Re: Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 16:02:59 GMT

Are these 2 databases combined into one table in the script? Will the date measured and date expected be linkable? If the below is hard to understand can you post a copy of your script?

Measured Table:

date(datemeasured) as Datelink

Expected Table:

date(dateexpected) as Datelink.

Line chart, 2 dimensions, 2 expressions

Thu, 03 May 2012 16:34:15 GMT

Hi, first you need link the 2 date fields, in this case only put the field date (containing values from date_measured and date_expected) as dimension.

Later use your 2 expressions:

Sum(expected_values)

Sum(measured_values)

Sorry for my bad english.

Regards.-

Line chart, 2 dimensions, 2 expressions

Fri, 04 May 2012 08:18:45 GMT

I linked the two data fields together as you can see from the solution.

Thanks everybody for your help!