how to add expressions and import pivot table to the same format in excel or power point

[savithri_n_s]

Hi ,

I have attached Book1 and qvw file which is what i am trying to do in Sheet 5 in qlik view

Part 1:Can you please let me know what is wrong with below expressions

# of Long Tenure Contractors (>2 yrs.) *** in excel sheet is calculated as below in qlik

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Tenure (months)]> {24}>}[Data Source])

# of High Cost Contractors (Bill Rate > $150/hr.) in excel sheet is calculated as below in qlik view

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Hourly Rate $]={'>$150.00'}>}[Data Source])

how to put this kind of expression in qlik view basically * and <>

COUNTIFS('Contractor Roster Data'!$AU$5:$AU$225000,$B$1,'Contractor Roster Data'!$A$5:$A$225000,"CX Managed",'Contractor Roster Data'!$C$5:$C$225000,"Effect*",'Contractor Roster Data'!$W$5:$W$225000,"<>EXTERNAL CHARGEABLE*",'Contractor Roster Data'!$W$5:$W$225000,"<>(Blank)")

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Bus Activity]<>{'EXTERNAL CHARGEABLE SERVICES','(Blank')}>}[Data Source])

And also how to calculate the percentage based on 2 expressions .Is this right

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Tenure (months)]> {24}>}[Data Source])/count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Hourly Rate $]={'>$150.00'}>}[Data Source])

Part 2:

Instead of the below

I want the below excluding Total

Part 3:

How to export the same pivot to excel or Power point in the same format

Regards,

Savithri

Message was edited by: savithri Ns I have attached the updated qvw file

[Digvijay_Singh]

This below needs correction -

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Tenure (months)]={">24"}>}[Data Source])

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Hourly Rate $]={">150"}>}[Data Source])

[Digvijay_Singh]

Remove total by unchecking partial sum as below -

[savithri_n_s]

Hi Digvijay,

Thanks. However using the below expression I am unable to get the value  in Sheet 5 of qvw file

column name # of Long Tenure Contractors (>2 yrs.) ***

1.

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Tenure (months)]={">24"}>}[Data Source])

2.

I want to get all values Bus Activity not equal EXTERNAL CHARGEABLE SERVICE and (Blank).Can you please tell me what is wrong with below expression

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Bus Activity]-={'EXTERNAL CHARGEABLE SERVICE','(Blank)'} >}[Data Source])

3.

[savithri_n_s]

3.

Below is the excel formula

COUNTIFS('Contractor Roster Data'!$AU$5:$AU$225000,$B$1,'Contractor Roster Data'!$A$5:$A$225000,"CX Managed",'Contractor Roster Data'!$C$5:$C$225000,"Effective*",'Contractor Roster Data'!$E$5:$E$225000,"United States",'Contractor Roster Data'!$AQ$5:$AQ$225000,">=9/1/2017",'Contractor Roster Data'!$AQ$5:$AQ$225000,"<=9/30/2017",'Contractor Roster Data'!$CC$5:$CC$225000,"Resources")

I want the dates to be changed dynamically based on each month it should take start and end date

what is wrong with below expression and how to change the date dynamically in expression

count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Tenure (months)]={">=9/1/2017" and

"<=9/30/2017"}>}[Data Source])

4. How can we sort the data  Current month/year should be Sept 2017 to Dec 2017 in straight table instead of Dec 2017 to Sept 2017

Attached the qvw for the same.