Expressions

[savithri_n_s]

Hi Sunny

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])

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 in expression as below

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])

Attached the qvw file

Regards,

Savithri

Thanks Sunny. I have attached the qvw file what is wrong with below expressions 1. (sum({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},sourcingmethod={'Requisitioned'}>} If([Assignment Start date] >=MonthStart(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')) and [Assignment Start date] <= MonthEnd(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')), [Data Source]))/ Count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},sourcingmethod={'Requisitioned'}>} If([Assignment Start date] >=MonthStart(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')) and [Assignment Start date] <= MonthEnd(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')), [Data Source]))) 2.count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Client - Normalized]={distinct([Client - Normalized])}>} And also it would be great if you can tell me how to add the average FY18 column in straight table Sep-17 Oct-17 Nov -17 Dec-17 Jan-18 Average FY18 Regards Savithri

[sunny_talwar]

New expression...

Count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]=  {'Effective','Effective-OnBoard'}>}

If([Assignment Start date] >=MonthStart(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')) and [Assignment Start date] <= MonthEnd(Date#(Left(SubField([Current Month/Year], ',', 1), 3) & ',' & SubField([Current Month/Year], ',', 2), 'MMM, YYYY')), [Data Source]))

But I also suggest you to fix your [Current Month/Year] in the script to avoid all the hassle we had to go through above