4 Replies Latest reply: Nov 18, 2015 9:21 AM by Friedrich Hofmann RSS

    Show data over all the available months when the user has selected only one?

    Friedrich Hofmann

      Hi,

       

      I have a straight table chart that shows the following:

      - The user can select any of the available months (available in the base file
         => The chart shows the hrs a forklift was in operation (the grand total)

      - Based on this, the month before (the one selected by the user) is generated
      ´  => The chart also shows the grand total of operating hrs at the end of the month before

      => Based on these two, the hrs. the forklift was in operation during this month are calculated

      - I know how many working days there were in any given month
         => The hrs a forklift was in operation per day (on average) is calculated

      - I know how many working hrs there are in one day
         => The utilization (in %) of a forklift in that month is displayed.

       

      Now I would like to include a mini-chart or so displaying the development of the utilization over all the months available in the list;
      => That means I will have to calculate the nr. of operating hrs in every month using the grand totals in the list.

       

      The question is, can I somehow do this directly on the GUI? My approach would be to just have one more table with some "inbetween calculations" in the script to feed this mini-chart, which would mean re-creating in the script some of the formulas that I already have on the GUI - but maybe there is a better/ more elegant way?

       

      Thanks a lot!

       

      Best regards,

       

      DataNibbler

        • Re: Show data over all the available months when the user has selected only one?
          Friedrich Hofmann

          Hi,

           

          I'm getting started on this in the script. The thing is, I don't know exactly how many columns with grand_totals of operating hrs (one per month) are in my base file - that is determined at runtime. I need a second table very similar to that one, so I'd rather just load it RESIDENT, that would be much faster. But

          => Can I load RESIDENT from a table all the fields that begin with a certain string - when I don't know the exact
                field_name nor how many fields of that kind there are?

           

          Thanks a lot!

           

          Best regards,

           

          DataNibbler

            • Re: Show data over all the available months when the user has selected only one?
              Friedrich Hofmann

              Hi,

               

              Okay. I have a table now with 13 fields or so - they are all labeled in this format:

              - >> Betriebsstunden Januar 2015 <<

              (starting from Dec 2014 and going through october obviously)

               

              Keep in mind, I have to assume that I don't know how many and which fields there are as it changes every month.

               

              Now I have to take all those fields in pairs of 2 and using these 2 total_operating_hrs, I can calculate the operating_hrs within one month - I have to start at 2 which is January 2015, so I have to subtract the grand_total of Dec_14 from the one in Jan_15.

               

              I have to do this in a loop.

              I paste here the code I have up to now in that loop.

              As you can see, the two fields are recognized and due to the $()-expansion they are expanded to their value; The third line which should hold the actual calculation does not work yet.

              Can someone help me there?

              I attach a small sample_file with some bogus data for this (only the first three months and an ID)

               

               

               

              FOR i = 2 TO 3 //Das Startfeld 2 entspr. dem Feld v_field_34, das ist der Januar des aktuellen Jahres
                j = $(i)+32;
              LET v_fieldname = chr(39) & chr(36) & '(v_field_' & $(j) & ')' & chr(39);
              LET v_fieldname2 = chr(39) & chr(36) & '(v_field_' & ($(j)-1) & ')' & chr(39);
              LET v_calc_new = $(v_fieldname) - $(v_fieldname2);
              NEXT i

               

              Thanks a lot!

               

              Best regards,

               

              DataNibbler

                • Re: Show data over all the available months when the user has selected only one?
                  Friedrich Hofmann

                  Hi,

                   

                  now I have the following code which almost works.

                   

                   

                  FOR i = 2 TO 3 //Das Startfeld 2 entspr. dem Feld v_field_34, das ist der Januar des aktuellen Jahres
                    j = $(i)+32;
                  LET v_field1 = chr(39) & 'v_field_' & $(j) & chr(39);
                  LET v_field2 = chr(39) & 'v_field_' & ($(j)-1) & chr(39);
                  LET v_calc = $(v_field1) & '-' & $(v_field2);
                  $(v_calc)
                  NEXT i 

                   

                  It now works up to the third line, the calculation is assembled almost correctly - but the $()-expansion shows $(v_field1) as v_field34 and $(v_field2) as v_field34 - so I need to expand them a second time. I'll try ...

                  I think there is a blog around here on that, isn't there? I just cannot find it - the search is quite difficult if you don't know the exact name of the post you search, you get all sorts of stuff you were not searching for ...