percentage calculation in the script

[qlikviewwizard]

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 script as shown below reports.

I want to show all the GRADES as shown in the screen

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:

[Kushal_Chawda]

Please see the attached

[Peter_Cammaert]

Change the expression into =COUNT(DISTINCT EMPNO) if you want the correct numbers (only 14 Employees)

[#4]

Please find attched file.

[jagan]

Hi,

Your grade table is not linked with the datamodel, so you should link this table with your actual data model, then only it works.

Regards,

jagan.

[jagan]

Hi,

Check this file for solution.

Regards,

Jagan.

[jagan]

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.

[qlikviewwizard]

Hi jagan ,kSindhuKannan and Kush141087,

Thank you for your solutions.

I should be joined SALTable,COMMTable as per the data.

I have to calculate the ComPercentage for EMPNO,INCHARGE1,INCHARGE2 SALs.

So that,I am using below script.

But unable to calculate ComPercentage.ComPercentage

Please help.

GRADE:
LOAD * INLINE [
SNo,GRADE
1,0%-10%
2,10%-20%
3,20%-30%
4,30%-40%
5,40%-50%
6,50%-60%
7,60%-70%
8,70%-80%
9,80%-90%
10,90%-100%
11,100%-110%
12,110%-120%
13,>120%];


SALTable:
load *,date(DATE) as Date,
month(date(DATE)) as Month,
'Q' & ceil(month(date(DATE))/3) as Quarter,
year(date(DATE)) as Year;
LOAD * INLINE [
EMPNO,ENAME,JOB,INCHARGE1,INCHARGE2,DATE,SAL,DEPTNO
7369,SMITH,CLERK,7902,7566,17-Dec-2015,700,20
7369,SMITH,CLERK,7902,7566,17-Dec-2015,900,20
7499,ALLEN,SALMAN,7698,7839,20-Feb-2015,1600,30
7499,ALLEN,SALMAN,7698,7839,20-Feb-2015,1900,30
7521,WARD,SALMAN,7698,7839,22-Feb-2015,0,30
7521,WARD,SALMAN,7698,7839,22-Feb-2015,1650,30
7566,JONES,MANAGER,7839,7698,2-Apr-2015,2975,20
7566,JONES,MANAGER,7839,7698,2-Apr-2015,3975,20
7654,MARTIN,SALMAN,7698,7788,28-Sep-2015,1250,30
7654,MARTIN,SALMAN,7698,7788,28-Sep-2015,2250,30
7698,BLAKE,MANAGER,7839,7698,1-May-2015,2850,30
7698,BLAKE,MANAGER,7839,7698,1-May-2015,3850,30
7782,CLARK,MANAGER,7839,7566,9-Jun-2015,0,10
7782,CLARK,MANAGER,7839,7566,9-Jun-2015,3450,10
7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,1250,20
7788,SCOTT,ANALYST,7566,7782,9-Dec-2015,4000,20
7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,5000,10
7839,KING,PRESIDENT,NULL,7698,17-Nov-2015,6000,10
7844,TURNER,SALMAN,7698,7788,8-Sep-2015,1500,30
7844,TURNER,SALMAN,7698,7788,8-Sep-2015,2500,30
7876,ADAMS,CLERK,7788,7698,12-Jan-2015,1100,20
7876,ADAMS,CLERK,7788,7698,12-Jan-2015,2100,20
7900,JAMES,CLERK,7698,7566,3-Dec-2015,950,30
7900,JAMES,CLERK,7698,7566,3-Dec-2015,650,30
7902,FORD,ANALYST,7566,7782,3-Dec-2015,3000,20
7902,FORD,ANALYST,7566,7782,3-Dec-2015,2000,20
7934,MILLER,CLERK,7782,7782,23-Jan-2015,1300,10
7934,MILLER,CLERK,7782,7782,23-Jan-2015,300,10
];


Concatenate (SALTable)
LOAD INCHARGE1 AS EMPNO,
DATE,
SAL AS INCHARGE1SAL,
month(DATE) as Month,
if(IsNum(DATE), 'Q'&ceil(month(date(DATE))/3)) as Quarter,
Year(DATE) as Year
Resident SALTable;


Concatenate (SALTable)
LOAD INCHARGE2 AS EMPNO,
DATE, 
SAL AS INCHARGE2SAL,
month(DATE) as Month,
if(IsNum(DATE), 'Q'&ceil(month(date(DATE))/3)) as Quarter,
Year(DATE) as Year
Resident SALTable;


Mapping_Table1:
Mapping Load EMPNO,SAL Resident SALTable;
Mapping_Table2:
Mapping Load INCHARGE1,SAL Resident SALTable;
Mapping_Table3:
Mapping Load INCHARGE2,SAL Resident SALTable;


COMMTable:


LOAD
*,
if(ComPercentage>=0 AND ComPercentage<0.1,'0%-10%',
if(ComPercentage>=0.1 AND ComPercentage<0.2,'10%-20%',
if(ComPercentage>=0.2 AND ComPercentage<0.3,'20%-30%',
if(ComPercentage>=0.3 AND ComPercentage<0.4,'30%-40%',
if(ComPercentage>=0.4 AND ComPercentage<0.5,'40%-50%',
if(ComPercentage>=0.5 AND ComPercentage<0.6,'50%-60%',
if(ComPercentage>=0.6 AND ComPercentage<0.7,'60%-70%',
if(ComPercentage>=0.7 AND ComPercentage<0.8,'70%-80%',
if(ComPercentage>=0.8 AND ComPercentage<0.9,'80%-90%',
if(ComPercentage>=0.9 AND ComPercentage<1.0,'90%-100%')))))))))) AS GRADE;




load * ,
ApplyMap('Mapping_Table1',EMPNO)/COMM as ComPercentage;
//ApplyMap('Mapping_Table2',INCHARGE1)/COMM as ComPercentage2,
//ApplyMap('Mapping_Table3',INCHARGE2)/COMM as ComPercentage3;


LOAD * INLINE
[
EMPNO,COMM
7369,150
7499,300
7521,
7566,150
7654,1400
7698,150
7782,
7788,200
7839,200
7844,0
7876,200
7900,200
7902,200
7934,300
];

[#9]

Your apply map should be like this,

load * ,

ApplyMap('Mapping_Table1',EMPNO)/COMM as ComPercentage,

ApplyMap('Mapping_Table2',EMPNO)/COMM as %SAL2,

ApplyMap('Mapping_Table3',EMPNO)/COMM as %SAL3;

Find attachment.

[jagan · Accepted solution]

Hi,

Please find attached file for solution.

Regards,

jagan.