Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart expression and input boxes

Hi

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?

MonthHours
Jan595620
Feb608760
Mar608760
Apr546448.3
May559050.2

IncidentIDIncidentTypeDateRaised
8655RTA (including company cars)03/01/2011
8656Injury / Accident02/01/2011
8677Injury / Accident04/01/2011
8688Injury / Accident03/01/2011
8690RTA (including company cars)05/01/2011
8692Injury / Accident05/01/2011
8700RTA (including company cars)05/01/2011
8712RTA (including company cars)06/01/2011
8714Injury / Accident05/01/2011
8721RTA (including company cars)06/01/2011
8722Injury / Accident06/01/2011
8731RTA (including company cars)06/01/2011
8741Injury / Accident07/01/2011
8742RTA (including company cars)07/01/2011
8745RTA (including company cars)07/01/2011
8747Injury / Accident09/01/2011
8750RTA (including company cars)10/01/2011
8753RTA (including company cars)07/01/2011
8757Injury / Accident08/01/2011
8759Injury / Accident09/01/2011
8767Injury / Accident10/01/2011
8786RTA (including company cars)11/01/2011
8787RTA (including company cars)11/01/2011
8788RTA (including company cars)10/01/2011
8789RTA (including company cars)05/01/2011

1 Solution

Accepted Solutions
Not applicable
Author

Is that what you wanna do ?

Tell me if it's not.

Regards,

View solution in original post

5 Replies
Not applicable
Author

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

Not applicable
Author

Is that what you wanna do ?

Tell me if it's not.

Regards,

Not applicable
Author

Hi

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

Not applicable
Author

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

Not applicable
Author

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,