Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |
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
Is that what you wanna do ?
Tell me if it's not.
Regards,
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
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
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,