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

[datanibbler]

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

[datanibbler]

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

[datanibbler]

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

[datanibbler]

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 ...

[datanibbler · Accepted solution]

Okay,

I finally figured it out myself.

I just removed  this loop, I am calculating the differences inbetween the fields in the actual LOAD now in case they are filled - if the fieldname  begins with "dummy", my difference_field says "NN", I will have to filter those out in an additional step.

Best regards,

DataNibbler