7 Replies Latest reply: Jan 13, 2016 9:48 AM by Mavrinder Dhothar RSS

    Avg for latest 2 dates expression



      I have made a table with the following:


      Dimension: Name

      Expression: Avg(Score)


      I want to be able to count the number of people with a Score above 3.5 (scores range between 1 - 5), but the Avg should only be taken from the latest 2 dates. This means I need to incorporate the >3.5 and latest 2 dates into my expression.


      Can anybody point me in the right direction?





        • Re: Avg for latest 2 dates expression
          Tresesco B

          May be like:


          Avg({<Score={'>3.5'}, Datefield={">=Date(Max(Datefield,2)) <=Date(Max(Datefield))"}>}Score)

            • Re: Avg for latest 2 dates expression
              Sunny Talwar

              I would just add one thing that if you Datefield has a different format compared to the one that is set by Date you will probably need to add the Datefield format in the Date function:


              =Avg({<Score={'>3.5'}, Datefield={">=Date(Max(Datefield,2), 'YourDateFieldFormat') <=Date(Max(Datefield), 'YourDateFieldFormat')"}>}Score)

              To check if the format is matching on not, just add Date(Max(Datefield)) in a text box to see if it is in the same format as DateField or not. If it is, then tresesco's solution should work as a charm, else make the slight change I just mentioned above.






                • Re: Avg for latest 2 dates expression

                  Hi Sunny,


                  Thanks for help. I've tried both ways and the table doesn't display any results. I am confused as to why it doesn't show anything.


                  Also, when I said I wanted to calculate the score above 3.5. I meant the overall average being above 3.5 for the last 2 dates. So if Sunny had scored 3 on 12.12.2015 and 4 on 01.01.2016, the average should be 3.5 but if we had:


                  Alan 01.01.2016    4

                  Alan 03.01.2016    2

                  Alan 04.01.2016    5


                  Then Alan should not appear in the list as his average is 3 based on the latest 2 dates.


                  Does this make sense?





                    • Re: Avg for latest 2 dates expression
                      Sunny Talwar

                      Oh then Score cannot be used within the set analysis:


                      May be something on these lines:


                      =If(Avg({<Datefield={"$(='>=' & Date(Max(Datefield,2), 'YourDateFieldFormat') & '<=' & Date(Max(Datefield), 'YourDateFieldFormat'))"}>}Score) > 3.5, Avg({<Datefield={"$(='>=' & Date(Max(Datefield,2), 'YourDateFieldFormat') & '<=' & Date(Max(Datefield), 'YourDateFieldFormat'))"}>}Score))

                        • Re: Avg for latest 2 dates expression

                          Thanks Sunny, this displays the results.


                          One final question. If I wanted to avoid any blank cells in the Datefield how would I do this?


                          for example,


                          Alan 01.01.2016    4

                          Alan 03.01.2016    4

                          Alan NULL CELL  


                          This would not appear in my table, but a 4 should appear.


                          Do you have any idea how I can include values for this scenario?


                          Much appreciated for the earlier answer!





                            • Re: Avg for latest 2 dates expression
                              Sunny Talwar

                              You mean the same expression above you want to avoid blank cells? I would think that the date range within the set analysis would automatically ignore blank dates. Is it not doing that or is this a stand alone requirement where you don't have any date range set analysis, but just need to ignore blank dates?

                                • Re: Avg for latest 2 dates expression

                                  I think the latter. My Source table would have the below


                                  Michael Incomplete
                                  Mav Incomplete


                                  but in my table with the expression what is currently happening is:


                                  NameAverage Score


                                  Sunny would not appear as his avg is 3. Michael and Mav should have 3.5 but it only takes the last figure as I believe the average is including the blank cell. Alan is the only correct one.