topic Re: need help in writing expression in QlikView

need help in writing expression

kunkumnaveen — Tue, 01 Mar 2016 11:24:56 GMT

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

Chanty4u — Tue, 01 Mar 2016 11:28:23 GMT

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

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

Re: need help in writing expression

kunkumnaveen — Tue, 01 Mar 2016 11:34:43 GMT

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

kunkumnaveen — Tue, 01 Mar 2016 11:39:30 GMT

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

Chanty4u — Tue, 01 Mar 2016 11:41:28 GMT

what is the variable value?

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

Re: need help in writing expression

sunny_talwar — Tue, 01 Mar 2016 11:42:33 GMT

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

Re: need help in writing expression

kunkumnaveen — Tue, 01 Mar 2016 11:57:15 GMT

Hello sunny ,

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

thanks

naveeen