Finding out the month with the max amount

[datanibbler]

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

Can anybody please help me do this?

Thanks a lot!

Best regards,

DataNibbler

[er_mohit]

i think you have to use firstsorted value function

[#3]

try any of below one

firstsortedvalue( Schadensmonat, -Aggr(sum(Schadenshöhe),Schadensmonat))  

max(aggr(sum(Schadenshöhe),Schadensmonat))

[datanibbler]

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:

>>  {<sum(Schadenshöhe) = {$(=max(aggr(sum(Schadenshöhe), Schadensmonat))))}>}  <<

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

[datanibbler]

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.

[datanibbler]

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:
LOAD
    Schadensmonat,
    sum(Schadenshöhe) as Gesamtschaden_Monat
Resident Int_Stapler
WHERE (num(Schadensmonat)=i)
GROUP BY Schadensmonat
;

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

[datanibbler]

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

>>  avg({<Gesamtschaden_Monat = {$(=max(Gesamtschaden_Monat))}>}Schadensmonat_num)  <<

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

[datanibbler]

And the winner is ...

>>  =  Month(Makedate('2013', avg({1<Gesamtschaden_Monat = {'$(=max(Gesamtschaden_Monat))'}>}Schadensmonat_num), '1'))  <<

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

[datanibbler]

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

>>  Month(MAKEDATE('2013', avg({1<Gesamtschaden_Monat = {'$(=max({1}Gesamtschaden_Monat))'}>}Schadensmonat_num), '1'))  <<

Now the month displayed (as the one with the highest total) is April, regardless of any user_selection.

Best regards,

DataNibbler