topic Re: percentage calculation front end in QlikView

percentage calculation front end

qlikviewwizard — Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I have two tables SALTable and COMMTable and want to do SAL % with COMM/SAL and GRADE.

SAL column have the nulls.

How to achieve the same in report front end as shown below reports.

Please help me. Thanks in advance.

SAL%:

=(Sum({<Year=, Quarter=, Month=,DATE={'>=$(=vStartDate)<=$(=vMaxDate)'}>} SAL)

(Sum({<Year=, Quarter=, Month=,DATE={'>=$(=vStartDate)<=$(=vMaxDate)'}>} COMM)))

GRADE:

if(SAL%>=0 AND SAL%<0.1,'0%-10%',

if(SAL%>=0.1 AND SAL%<0.2,'10%-20%',

if(SAL%>=0.2 AND SAL%<0.3,'20%-30%',

if(SAL%>=0.3 AND SAL%<0.4,'30%-40%',

if(SAL%>=0.4 AND SAL%<0.5,'40%-50%',

if(SAL%>=0.5 AND SAL%<0.6,'50%-60%',

if(SAL%>=0.6 AND SAL%<0.7,'60%-70%',

if(SAL%>=0.7 AND SAL%<0.8,'70%-80%',

if(SAL%>=0.8 AND SAL%<0.9,'80%-90%',

if(SAL%>=0.9 AND SAL%<1.0,'90%-100%')

Required Report Output:

Re: percentage calculation front end

awhitfield — Thu, 25 Jun 2015 11:02:46 GMT

Hi most of it's done in to attached, you had an unwanted + in the sal% expression.

HTH - Andy

Re: percentage calculation front end

qlikviewwizard — Thu, 25 Jun 2015 11:13:58 GMT

Hi awhitfield,

Thanks for the quick solution.

How to achieve the below one in report?

I don't have SAL % in script.

Kindly help.

Report2

SAL %	No of Emps
0%-10%	14
10%-20%	0
20%-30%	0
30%-40%	0
40%-50%	1
50%-60%	0
60%-70%	0
70%-80%	0
80%-90%	0
90%-100%	0

Re: percentage calculation front end

buzzy996 — Thu, 25 Jun 2015 11:18:27 GMT

Try let me us know,if it works.

sal% :

=Sum({<Year=, Quarter=, Month=,DATE={'>=$(=vStartDate)<=$(=vMaxDate)'}>}if(isnull(COMM),0,COMM))

Sum({<Year=, Quarter=, Month=,DATE={'>=$(=vStartDate)<=$(=vMaxDate)'}>}if(isnull(SAL),0,SAL))

Re: percentage calculation front end

qlikviewwizard — Thu, 25 Jun 2015 11:24:03 GMT

Hi ,

I am getting as shown below:

But I want to show like below:

SAL %	No of Emps
0%-10%	14
10%-20%	0
20%-30%	0
30%-40%	0
40%-50%	1
50%-60%	0
60%-70%	0
70%-80%	0
80%-90%	0
90%-100%	0

Re: percentage calculation front end

Kushal_Chawda — Thu, 25 Jun 2015 11:42:48 GMT

Please see the attached one

Re: percentage calculation front end

qlikviewwizard — Thu, 25 Jun 2015 11:46:07 GMT

Hi Kush141087,

I want to show all the GRADES even data is NULL.

GRADE	No of Emps
0%-10%	14
10%-20%	0
20%-30%	0
30%-40%	0
40%-50%	1
50%-60%	0
60%-70%	0
70%-80%	0
80%-90%	0
90%-100%	0

Please advise.

Re: percentage calculation front end

Kushal_Chawda — Thu, 25 Jun 2015 12:10:50 GMT

As % is calculated based on expression, the values which falls in particular grade only that % will be captured.

If you want to achieve the same result then it is possible in script only not on front end if I am not wrong

Re: percentage calculation front end

qlikviewwizard — Thu, 25 Jun 2015 12:19:35 GMT

Hi Kush141087,

Please help to reply the answer at this thread percentage calculation in the script

Re: percentage calculation front end

Kushal_Chawda — Thu, 25 Jun 2015 13:05:20 GMT

I have replied on this thread

Re: percentage calculation front end

jagan — Fri, 26 Jun 2015 03:00:17 GMT

PFA file for solution.

Regards,

Jagan.

Re: percentage calculation front end

jagan — Fri, 26 Jun 2015 03:01:22 GMT

Always remember that all the tables in the Datamodel should be linked otherwise if you use those dimensions the values won't split according to dimension.

Regards,

Jagan.

Re: percentage calculation front end

qlikviewwizard — Sat, 27 Jun 2015 11:32:21 GMT

Thank you All.