I want to assemble the following expression, but I cannot quite get there:
I have a list of damage_events from Jan till now. Using the AGGR function, I can "group" the damage_amounts by month and thus see which month has which damage_amount.
The code for this is >> aggr(sum(Schadenshöhe), Schadensmonat) << or something like it - it's not working right now, but I already had it working...
I want to take this a step further: I want to display the name (and the total amount) of the month which, according to this expression, had the highest total amount yet.
Can I do this at all purely on the GUI level, or will I have to assemble a compacted table in the script with just one record per month (I cannot use the AGGR fct in the script, so I'd have to use a LOOP and WHERE clauses to have one LOAD statement per month with a SUM of the amount?)
the latter approach works - that was actually the one I had at a time - I haven't tried the first yet. I just have to ignore the automated selections on that sheet and I get the highest damage amount. So I have to built that into a set_analysis expression somehow - sth. like, but not quite:
Does anything come to your mind as to why this isn't working or how I could do this?I guess this isn't working because >sum(Schadenshöhe)< means ALL the damage_amounts until now which of course is not equal to that - how could I do this?
using that FIRSTSORTEDVALUE fct would require that I have a compact table with only one record (and one sum) per month - so I would have to build a LOOP in the script. That would probably be a possibility.
I would have liked to do it on the GUI-level to avoid my data_model becoming too big, but I guess it can't be helped then.
I am now about to get started on that LOOP. I am not too experienced in constructing that kind of LOOP, however. Could somebody lend me a little hand there, please?
- I have in the script implemented the MONTH() fct to generate the month of an event from the list.
- Every one of these months has a numeric value, so I can use that:
-- I have to somehow find out in the script which is the current month (num 6 at the moment)
-- Then I can write a LOOP querying the month of an event line-wise and "grouping" them
- When I have a generic LOAD statement, I can use a SUM function to get the total for that month
- I would have to CONCATENATE all of these generic LOADs into one compacted table with currently six records.
Does that sound/ look about right?
I will have a look at one such LOOP a consultant had built for us in another place and try to adapt this.
Well - so far it doesn't work. I currently have a code like this:
DO WHILE i <= 12
Monatssummen: LOAD Schadensmonat, sum(Schadenshöhe) as Gesamtschaden_Monat Resident Int_Stapler WHERE (num(Schadensmonat)=i) GROUP BY Schadensmonat ;
The script runs all right, but this does not seem to be executed at all. I have only six months in the list, but it does no harm and it doesn't take up too much CPU-time to let the script run empty six times, does it?
I am now one step further: The loop is executed all right and I get a nice table with six records, two fields: The name_of_month and the total amount. I can select the max value in a textbox all right - but now I want to incorporate that into a set_analysis_expression and display the name of the month where the month_amount was greatest.
The only problem is, I cannot make that independent of the selection - without any selection, it says "April" (correct), but when June is selected (there is a trigger on opening the sheet), it says "June"...
Does anyone know how to do that? The parameter 1 seems to not have any effect, I suppose it is too far inside. I'd need it on the outside, but I can't think of any aggregation_fct I can apply to a Month_in_text_form.