8 Replies Latest reply: Jul 1, 2013 7:20 AM by Friedrich Hofmann

# Finding out the month with the max amount

Hi,

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?)

Thanks a lot!

Best regards,

DataNibbler

• ###### Re: Finding out the month with the max amount

i think you have to use firstsorted value function

• ###### Re: Finding out the month with the max amount

try any of below one

• ###### Re: Finding out the month with the max amount

Hi kalraja4,

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?

Thanks a lot!

Best regards,

DataNibbler

• ###### Re: Finding out the month with the max amount

Hi,

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.

• ###### Re: Finding out the month with the max amount

Hi,

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:
Resident Int_Stapler
;

LOOP

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?

Thanks a lot!

Best regards,

DataNibbler

• ###### Re: Finding out the month with the max amount

Hi,

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.

How can that be done? My current code

does not yet work - avg(Schadensmonat_num) returns a correct result if I select a month, so that part is good.

Can anybody help me there?

Thanks a lot!

Best regards,

DataNibbler

• ###### Re: Finding out the month with the max amount

And the winner is ...

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.

Thanks a lot!

Best regards,

DataNibbler

• ###### Re: Finding out the month with the max amount

This is also done.

I got a hint from our external consultant - I just needed that parameter 1 once more within the max-fct - so the final formula is