6 Replies Latest reply: Sep 29, 2011 2:30 AM by Willem Classens RSS

    Last 10 entries

    Willem Classens

      I would like to calculate the avarage of the last 10 entries (date_value). I have a Date, and an extra value attached to that date.

      so 24-09-2011 with value "123".

       

      Thanks to an other topic, I thought I found a solution after modifications.

       

       

      =IF(

      aggr(

      rank(total

      aggr( (date_value),date_value)

      ,0,1)

      ,date_value)

      <=10,

       

      aggr(

      rank(total

      aggr( (date_value),date_value)

      ,0,1)

      ,

      date_value))

       

      But I noticed this wasn't the solution. It shows the last 10 entries in a table, but I'm planning on creating a Gauge which should display the avarage of these last 10 items.

      Instead, if I place avg() in the above code, it shows the avarage of all values together. Not just the most recent 10 dates.

       

      What expression should I use to get the last 10 entries (date), so I could calculate with just these 10 entries?

       

      Thanks in advance

        • Last 10 entries
          swuehl

          Hi,

           

          I think a

          =avg(aggr(if(rank(date)<=10, date_value), date))

           

          should give you the average of the values for the last 10 dates (date is a Date format, with a numerical representation, right?).

           

          Regards,

          Stefan

            • Last 10 entries
              Willem Classens

              hmm how should I implement this?

              Delete all the code I had and just put this code in place as calculated dimension?

              Because I get no value then.

               

              Maybe I didn't explain it correctly,

              but the dates all have a value.

               

              So for example (5 dates for ease)

              Date          | Value  

              27-09-2011 | 123

              13-09-2011 | 119

              24-08-2011 | 116

              22-08-2011 | 122

              01-08-2011 | 110

               

              The avarage should be the value of those (123+119+116+122+110) /5 = 118

               

              The dates come from an Excel file where they have a Date Format.

              Load DD as date_value

                • Last 10 entries
                  swuehl

                  Try putting above expression in a Text box.

                  your dates needs to be recognized as such by QV, i.e. a num(date) e.g. as calculated Dimension should return numerical values.

                    • Re: Last 10 entries
                      Willem Classens

                      I really can't get it to work.

                      Just for information.

                      In my Original Excel sheets I have column A which has different Dates in it

                      e.g.

                       

                       

                      27-09-2011

                      13-09-2011

                      24-08-2011

                      22-08-2011

                      01-08-2011

                       

                       

                       

                      they are read in the following way:

                      Load DD as date_value

                       

                      In QV it shows the dates nice and correctly if I select =date_value.

                      It's shown the exact same way, so

                      27-09-2011

                      13-09-2011

                      24-08-2011

                      22-08-2011

                      01-08-2011

                       

                      If I use your code, should I leave "date" or should I  create someting else from it.

                      This because if I leave it date, the date_value doesn't get the redlike color.

                       

                      I click new Chart and place this code in it via Calculated Dimension

                      I get an "// error in calculation dimension" error

                       

                      As expression I then use =avg(Rv)  (which is that value of which I want the average, those 123, 119, 116 etc.)

                       

                      So I guess I'm doing something wrong?

                       

                      Tried all different things with this piece of code, but till now, no success.

                        • Re: Last 10 entries
                          swuehl

                          I am sorry, I assumed date_value is the field name for the value associated with that date, if it is your date field, and RV your value field, you need to change to

                           

                          =avg(aggr(if(rank(date_value)<=10, Rv), date_value))

                           

                          Put this expression in a textbox or use it as expression in your gauge chart (not as calculated dimension, why do you need a calculated dimension here?).

                           

                          I attached a sample, I hope you are able to open the file and not working on a personal edition.

                           

                          Your date_value needs to be recognizes as Date type, as said, and unfortunately it will display correctly even if not (because it might be interpreted as string then).

                           

                          I assume the date format you have given above is also set in your script with

                           

                          SET DateFormat='DD-MM-YYYY';

                           

                          ??

                           

                          If so, your date_value is almost certainly a Date type. To check, you could create e.g. a year field by using

                           

                          LOAD

                          ...

                          year(DD) as Year,

                          ...

                          From ...;

                           

                          This only works correctly if DD (you need to use the original field name from excel here, date_value is yet unknown) is recognized as a Date.

                           

                          Hope this helps,

                          Stefan