Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
sum (agg({<ROLE={'SELF'}, DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE))
or
sum(if ({<ROLE={'SELF'}, DATE_OF_REVIEW={">=$(vfromdate)<=$(vtodate)"}>}TOTAL_SCORE))
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
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
what is the variable value?
$(vfromdate)<=$(vtodate)"}
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
Hello sunny ,
it worked man ...........bang on
thanks
naveeen