5 Replies Latest reply: Jun 30, 2011 5:04 PM by FERRIER Guillaume RSS

    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

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        • Chart expression and input boxes

          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

              Is that what you wanna do ?

              Tell me if it's not.

               

              Regards,

                • Chart expression and input boxes

                  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

                   

                   

                    • Chart expression and input boxes

                      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

                        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,