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

    Finding out the month with the max amount

    Friedrich Hofmann

      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

        • Re: Finding out the month with the max amount
          Mohit Sharma

          i think you have to use firstsorted value function

          • Re: Finding out the month with the max amount
            Raja Kaliyamoorthy

            try any of below one

             

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

             

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

              • Re: Finding out the month with the max amount
                Friedrich Hofmann

                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

                  • Re: Finding out the month with the max amount
                    Friedrich Hofmann

                    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
                        Friedrich Hofmann

                        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

                          • Re: Finding out the month with the max amount
                            Friedrich Hofmann

                            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

                              • Re: Finding out the month with the max amount
                                Friedrich Hofmann

                                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

                                  • Re: Finding out the month with the max amount
                                    Friedrich Hofmann

                                    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