topic Re: Chart expression and input boxes in QlikView

Chart expression and input boxes

Thu, 30 Jun 2011 15:25:14 GMT

I'm having troube getting my head around expressions.

Basically I have a simple table full of incident informaiton like the table below

I need to calculate the following

No of Injury/accident per month/Hours per month *1,000,000

Now I've got this far,

On the load script I've got

LOAD

IncidentID,

IncidentType,

DateRaised,

Year(DateRaised) as Year,

Year(DateRaised) as Month

I've then got a chart with the Month as a dimension and then an expression

count({<IncidentType={'*Injury*'}>}IncidentType)

this works fine

Now I have another table with the month and Hours, like below (ideally I'd like this to be an input box) but can live with it being an excel file at the moment

How do I now multipy the monthly totals from the chart expression above with the Hours figure in the table below?

Month	Hours
Jan	595620
Feb	608760
Mar	608760
Apr	546448.3
May	559050.2

IncidentID	IncidentType	DateRaised
8655	RTA (including company cars)	03/01/2011
8656	Injury / Accident	02/01/2011
8677	Injury / Accident	04/01/2011
8688	Injury / Accident	03/01/2011
8690	RTA (including company cars)	05/01/2011
8692	Injury / Accident	05/01/2011
8700	RTA (including company cars)	05/01/2011
8712	RTA (including company cars)	06/01/2011
8714	Injury / Accident	05/01/2011
8721	RTA (including company cars)	06/01/2011
8722	Injury / Accident	06/01/2011
8731	RTA (including company cars)	06/01/2011
8741	Injury / Accident	07/01/2011
8742	RTA (including company cars)	07/01/2011
8745	RTA (including company cars)	07/01/2011
8747	Injury / Accident	09/01/2011
8750	RTA (including company cars)	10/01/2011
8753	RTA (including company cars)	07/01/2011
8757	Injury / Accident	08/01/2011
8759	Injury / Accident	09/01/2011
8767	Injury / Accident	10/01/2011
8786	RTA (including company cars)	11/01/2011
8787	RTA (including company cars)	11/01/2011
8788	RTA (including company cars)	10/01/2011
8789	RTA (including company cars)	05/01/2011

Chart expression and input boxes

Thu, 30 Jun 2011 19:25:16 GMT

Hello Melanie,

First of all, I think it's juste a mistake but in your script your wrote "Year(DateRaised) as Month" instead of "Month(DateRaised) as Month".

I'm not sure to get what you wanna do. But I think you need to link the month you have in your table month/hours with the field month you create in your loading script.

Try that and tell me.

I'll also try to see if it works.

Guillaume

Re: Chart expression and input boxes

Thu, 30 Jun 2011 20:00:12 GMT

Is that what you wanna do ?

Tell me if it's not.

Regards,

Chart expression and input boxes

Thu, 30 Jun 2011 20:35:16 GMT

Thanks, I think its just a typo

Yes I think I need to link the 2 month fields. I can't open your QVW as I'm using a personal Edition

Chart expression and input boxes

Thu, 30 Jun 2011 20:58:37 GMT

Just looking at your excel file, I've noticed that the months are called 1,2,3 instead of Jan, Feb......

I think that has been where I've gone wrong,

Brilliant

Re: Chart expression and input boxes

Thu, 30 Jun 2011 21:01:59 GMT

Okay, so I will try to explain what I did in detail.

As I said, you need to link the two fields month, I first try to just load the month and table, but I had a problem. I don't really know why but the link between the month fields didn't work.

So I change the month in the table month-hours to that :

Month Hours

1 595620

2 608760

3 608760

4 546448.3

5 559050.2

Hope it's not a problem.

Then I wrote the script :

LOAD Month(Date('01/'&Month&'/2010')) as Month,

// here I recreate the month with the value, just to be sure it will fit with the other month

// there is certainly a better function which could do that but I'm quite new in QV 🙂

// I put 01 and 2010 but it really doesn't matter, this code is just to get the month correctly

Hours

FROM

.\Melanie.xlsx

(ooxml, embedded labels);

LOAD IncidentID,

IncidentType,

Date,

Year(Date) as Year,

Month(Date) as Month

FROM

.\Melanie.xlsx

(ooxml, embedded labels, table is Feuil2);

At the end you can try this expression : count({<IncidentType={'*Injury*'}>}IncidentType) / Hours * 1000000

With the solution, I think it works, if I have well understood what you wanna do 😉

Regards,