6 Replies Latest reply: Mar 1, 2016 6:57 AM by naveen kumar RSS

    need help in writing expression

    naveen kumar

      Hello all ,

        i have got about 800 suppilers(field =name1)  ,each suppiler has  a role(filed =ROLE)   based up on there role each suppiler as total score(field=TOTAL_SCORE) and each suppiler also got date field(DATE _OF_REVIEW)

       

      as i cant show all 800 suppilers at a time on the dashboard ,so i thinking to apply date filters on it

                                                                          1.from date=vfromdate

                                                                           2.todate=vtodate

      by using this filters i can reduce the suppliers,

      my requirement is ......i need to display only those suppliers and there  two diff total score based up ROLE field   within the selected date range  ...

       

      if same supplier within selected date range comes  more then once then get the latest date(max (date)) total score... what i mean is

       

      suppose from the date filter we select these dates: fromdate:01/01/2016

                                                                                to date:01/31/2016  

       

                                                             name1       DATE_OF_REVIEW   ROLE         TOTAL_SCORE

                                                              naveen              01/03/2016          SELF                    100

                                                               naveen              01/03/2016         AUDITOR                50

                                                                yyyy                 01/05/2016         SELF                      200

                                                                 yyyy               01/05/2016         AUDITOR                300

                                                                naveen              01/25/2016          SELF                    1000

                                                                  naveen              01/29/2016         AUDITOR               500

       

       

      OUT PUT:in any chart                                                max(date)                                          max(date)

                                            name1                          selfrole totalscore(label)                auditorrole totalscore(label)

                                             naveen                             1000                                                         500

                                                yyyy                                 200                                                         300

       

       

       

      to achieve this wat i am thinking is   :in a striaght table

                                   dimension :name1

                                    expression1:   ? ({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE)

                                                          ?what function has to be over there.....sum,if,aggr

                                     expression2:? ({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE)

                                                          ?what function has to be over there.....sum,if,aggr

      please find the attachment (as per now i have only 6 suppilers records but i move my app to production there will be data for all)

       

      thanks

      naveen

        • Re: need help in writing expression
          Chanty 4u

          sum (agg({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE))

          or


          sum(if ({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE))

            • Re: need help in writing expression
              naveen kumar

              hello chanty ,

               

              do i really has to write sum function over there?

              because i need max(date) total score  only ......writing sum function might add all the same supplier of that month

               

              what i am looking is this o/p

               

              OUT PUT:in any chart                                                max(date)                                          max(date)

                                                    name1                          selfrole totalscore(label)                auditorrole totalscore(label)

                                                     naveen                             1000                                                         500

                                                        yyyy                                 200                                                         300



            • Re: need help in writing expression
              naveen kumar

              sorry the example table looks a bit messed up

                                                           

                                                                      name1       DATE_OF_REVIEW   ROLE         TOTAL_SCORE

                                                                      naveen             01/03/2016             SELF                            100

                                                         naveen          01/03/2016          AUDITOR               50

                                                                       yyyy                       01/05/2016             SELF                              200

                                                                       yyyy                      01/05/2016          AUDITOR                 300

                                                         naveen              01/25/2016         SELF                    1000

                                                                       naveen              01/29/2016         AUDITOR               500

              • Re: need help in writing expression
                Sunny Talwar

                Try this:

                 

                FirstSortedValue({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE, DATE_OF_REVIEW)

                FirstSortedValue({<ROLE={'AUDITOR'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE, DATE_OF_REVIEW)


                FirstSortedValue({<ROLE={'SELF'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>} TOTAL_SCORE, -DATE_OF_REVIEW)

                FirstSortedValue({<ROLE={'AUDITOR'},  DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>} TOTAL_SCORE, -DATE_OF_REVIEW)


                UPDATE1: Look here for more information on FirstSortedValue: Value Associated with Min/Max Value of Another Field (Front End Solution)


                UPDATE2: Forgot to add the negative sign in front of DATE_OF_REVIEW field in FirstSortedValue() function