topic Re: how to write expression for percentage in QlikView

how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 05:11:11 GMT

Hello All,

suppose if i have a table data like this

supplier accepted rejected exception accepted total ENSTEHDAT

a 50 25 25 100 10/03/2015

a 100 50 50 200 15/004/2015

b 25 25 50 100 10/03/2015

b 50 25 25 100 15/004/2015

what my requirement is i want known what percentage of each supplier lots were accepted,rejected, exception from total for that particular period

i mean suppose i select date range from 01/03/2015 to 01/05/2015,and accepted value from decision field then i should see

accepted= 150(50%) (out of 300) 75(40%) (out of 200)

a b

rejected value from decision field rejected= 75(25%) (out of 300) 50(20%) (out of 200)

a b

exception accepted value from decision field exception accepted= 75(25%) (out of 300) 75(40%) (out of 200)

a b

so in a bar chart if i take two dimension:1)supplier

2)decision (as values accepted, rejected ,exception accepted)

expression :1)=count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>total)

2)percentage?

thanks

naveen

Re: how to write expression for percentage

jonathandienst — Thu, 07 Apr 2016 05:27:42 GMT

There is no decision field, so you will need to do this as three expressions:

For accepted:

Count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} TOTAL <supplier> accepted) /
      Count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} TOTAL <supplier> total)

And do the same for the other two decision values

Re: how to write expression for percentage

sujeetsingh — Thu, 07 Apr 2016 05:29:06 GMT

Naveen,

Fist of all there is two way when you say when I select that means the percentage is without any dimension that means you are not distributing the percentage on any dimension. Hence to represent this you need a text box object and quote your expression for each accepted ,rejected and exception .

Now the next thing is to distribute it on supplier .

Hence it can be a straight table.

Re: how to write expression for percentage

sujeetsingh — Thu, 07 Apr 2016 05:34:50 GMT

See this sample please

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 05:41:12 GMT

is it possible to write single dynamic expression for three values (accepted,rejected,exception accepted)

because i need to display dimensions 1)supplier

2)decision

is it possible by keeping two dimesion and single expression instead of three

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 05:42:49 GMT

i am showing it in bar chart so i need to show same with two dimensions

1)supplier

2)decision

Re: how to write expression for percentage

perumal_41 — Thu, 07 Apr 2016 05:46:23 GMT

Hi Navven,

Try below for percentage expr

Sum({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}total)

/Sum({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total total)

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 06:02:46 GMT

this expression compare and show percentage of only suppliers of that chart,but i need percentage expression which compare between the values of decision filed of same supplier

i mean suppose i select date range from 01/03/2015 to 01/05/2015,and accepted value from decision field then i should see

accepted= 150(50%) (out of 300) 75(40%) (out of 200)

a b

rejected value from decision field rejected= 75(25%) (out of 300) 50(20%) (out of 200)

a b

exception accepted value from decision field

exception accepted= 75(25%) (out of 300) 75(40%) (out of 200)

a b

Re: how to write expression for percentage

perumal_41 — Thu, 07 Apr 2016 06:12:01 GMT

Now Try

Sum({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}total)

/Sum({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total <[decision filed]> total)

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 06:24:38 GMT

hi ,please find the attachment ,with slides,if you see that a supplier called JAY Ganesh Chemicals

for this supplier i am getting accepted%=66.7

rejected%=60

exception%=50

total 176.7% but it has to be altogether 100% only

your expression:

count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

/count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total <Decision>PRUEFLOS)

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 06:30:27 GMT

Hello All,

Any more helps in achieving this requirement

please

Re: how to write expression for percentage

perumal_41 — Thu, 07 Apr 2016 06:46:03 GMT

Try now

count({$<YEAR=,QUATERS=,name1=,Decision=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

/count({$<YEAR=,QUATERS=,name1=,Decision=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total <Supplier Name>PRUEFLOS)

Re: how to write expression for percentage

Thu, 07 Apr 2016 06:50:11 GMT

Ur expression should be

=sum({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>total)/ sum(TOTAL total)

Also , select the relative checkbox in the expression tab (to show it in percentage).

Regards,

Anjali Gupta

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 06:55:37 GMT

dude ,i am getting an error,after that expression

dimensions:1)name1 (supplier)

2)decision

expression1=count({$<YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

wxpression2 = count({$<YEAR=,QUATERS=,Decision=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

/count({$<YEAR=,QUATERS=,Decision=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total <name1>PRUEFLOS)

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 07:00:42 GMT

No it not working ,i hope u known the requirement ,please go through the slide it clear up the requirement

Re: how to write expression for percentage

perumal_41 — Thu, 07 Apr 2016 07:17:34 GMT

I have attached application / please look it

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 07:20:49 GMT

so i dont have licence version,if it is expression can u write it down for me,thanks

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 07:21:18 GMT

so i dont have licence version,if it is expression can u write it down for me,thanks

Re: how to write expression for percentage

perumal_41 — Thu, 07 Apr 2016 07:25:10 GMT

same expression only

Sum(Value)
/Sum({<Decesion=>} Total <supplier>Value)

screen shot look like

with out selection

With supplier selection A

Re: how to write expression for percentage

kunkumnaveen — Thu, 07 Apr 2016 08:28:10 GMT

IF this is working for you ,then i think problem is from my side ,i mean i haven't clear out how my data is,

this requirement is a level 2 requirement , what i mean is after clicking on level 1 it will bring me to level2

so category and decision filed are selected at level1 based up on that selection the level 2 shows only those supplier which full fill thet category and decision values

level1 dimension:1)category

2)decision

expression

=count({$<MaterialName=,YEAR=,QUATERS=,name1=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

level2 dimension:1)name1

2)decision

expression:1)=count({$<YEAR=,QUATERS=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

count({$<YEAR=,QUATERS=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>}PRUEFLOS)

/count({$<YEAR=,QUATERS=,ENSTEHDAT={">=$(vFromdate)<=$(vTodate)"}>} Total <name1>PRUEFLOS) (your expression)

so do i need to add anything more