need help in writing expression

[kunkumnaveen]

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

[Chanty4u]

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

or

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

[kunkumnaveen]

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

[kunkumnaveen]

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

[Chanty4u]

what is the variable value?

$(vfromdate)<=$(vtodate)"}

[sunny_talwar · Accepted solution]

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

[kunkumnaveen]

Hello sunny ,

it worked man ...........bang on

thanks

naveeen