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

            • Re: Last 10 entries
              Willem Classens

              Thanks Stefan,

              This was the solution.

              I believe I tried this modification once, but I had an dimension added to it.

              I deleted the dimension and indeed the correct value showed up.

               

              Thanks a lot!