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

    Avg for latest 2 dates expression

      Hi,

       

      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?

       

      Thanks

       

      Mav

        • 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.

               

              HTH

               

              Best,

              Sunny

                • 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?

                   

                  Best,

                   

                  Mav

                    • 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!

                           

                          Regards

                           

                          Mav

                            • 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

                                     

                                  NameDateScoreStatus
                                  Michael01.01.20164Complete
                                  Michael02.01.20163Complete
                                  Michael Incomplete
                                  Alan01.01.20165Complete
                                  Alan03.01.20164Complete
                                  Alan05.01.20165Complete
                                  Sunny01.01.20165Complete
                                  Sunny02.01.20162Complete
                                  Sunny03.01.20164Complete
                                  Mav01.01.20164Complete
                                  Mav03.01.20163Complete
                                  Mav Incomplete

                                   

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

                                     

                                  NameAverage Score
                                  Michael3
                                  Alan4.5
                                  Mav3

                                   

                                  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.