6 Replies Latest reply: Jan 27, 2017 6:43 AM by Stefan Wühl RSS

    Max Wert

    Kevin Schütz

      Hello together,

       

      I need your help. I have the following function:

      Max(Datenpunkt_ID)

      it´s show me the correct answer with 214.

       

      If I use the function:

      Max(Datenpunkt_ID)-10

      It´s show me the answer 204, thats correct

       

      Now I need a function that I can delete the 10 high values, because my Datenpunkt_ID table look like this:

      Datenpunkt_ID

      214

      214

      214

      213

      212

      212

      212

      211

      210

      209

      208

      207

      208

       

      Has anybody an idea?

        • Re: Max Wert
          Stefan Wühl

          Not sure what you mean with '10 high values' with regard to your calculation of Max(Datenpunkt_ID)-10, but maybe something like this?

           

          DP:

          LOAD * INLINE [

          Datenpunkt_ID

          214

          214

          214

          213

          212

          212

          212

          211

          210

          209

          208

          207

          208

          203

          201

          200

          ];

           

          LEFT JOIN

          LOAD Max(Datenpunkt_ID)-10 as MaxID

          Resident DP;

           

          Final:

          LOAD Datenpunkt_ID

          Resident DP

          Where Datenpunkt_ID < MaxID;

           

           

          DROP TABLE DP;

            • Re: Max Wert
              Kevin Schütz

              I need the result of 209. So I look for a function with a resolve.

              The 10 highest max values should not be considered

              • Re: Max Wert
                Tim Driller

                i think you should Change this part from

                 

                LEFT JOIN

                LOAD Max(Datenpunkt_ID)-10 as MaxID

                Resident DP;

                //that will exclude the ID's > 204, which dont have to be the last 10 values

                 

                to

                 

                LEFT JOIN

                LOAD Max(Datenpunkt_ID, 10) as MaxID

                Resident DP;

                //this will get you the real last 10 max values

                  • Re: Max Wert
                    Stefan Wühl

                    I think Max(Datenpunkt_ID,10) won't consider duplicates correctly.

                     

                    Try

                    DP:

                    LOAD * INLINE [

                    Datenpunkt_ID

                    214

                    214

                    214

                    213

                    212

                    212

                    212

                    211

                    210

                    209

                    208

                    207

                    208

                    203

                    201

                    200

                    ];

                     

                     

                    First 1 LOAD Datenpunkt_ID as MaxID

                    Resident DP

                    WHERE Recno() >= 10

                    ORDER BY Datenpunkt_ID desc

                    ;

                      • Re: Max Wert
                        Kevin Schütz

                        Stefan your answer look correct with recno>=10

                        but how I can use the function in KPI

                         

                        And Tim with your function I get the wrong answer, with duplicates include.

                          • Re: Max Wert
                            Stefan Wühl

                            Maybe like

                             

                            =Max( Aggr( If(Rangesum(Above(Count(Datenpunkt_ID),0,Rowno())) >=10, Datenpunkt_ID), Datenpunkt_ID))

                             

                            This assumes Datenpunkt_IDs are loaded in order desc.

                            If this is not the case, you would need to create a correct load order in the script or use sorted aggr() dimension.

                            The sortable Aggr function is finally here!

                             

                            Instead of the Rangesum(Above(...)) part, you can also create a record counter in the resident table using Recno() and check against this field.