Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think your Tenure (months) field is stored as date in qlikview. Formatting is important for set analysis... to fix this... load the field like this
Num([Tenure (months)]) as [Tenure (months)]
Once you do this, your expression will automatically be fixed
Which expression are you talking about for your second requirement above?
Hi Sunny,
Thank you
In the first expression below i want all except Bus Activity not equal to external chargeable Services and (Blank)
1.count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Bus Activity]-={'EXTERNAL CHARGEABLE SERVICE','(Blank)'} >}[Data Source])
In the second expression below what is wrong And also i want the Assignment start date columns to be dynamic expressions as the formula should display value based in the dynamic field as in the table below
If the month name is Jan 2018 Assignment start date should be >=01/01/2018 and <=31/01/2018 and respectively for each month
2.count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Assignment start date]={">=9/1/2017" and
"<=9/30/2017"}>}[Data Source])
Regards,
Savithri
Try this for second
Count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},[Bus Activity]-={'EXTERNAL CHARGEABLE SERVICES','*Blank*'} >}[Data Source])
I don't see the third expression being used anywhere, but try this
Count({<[Data Source] = {'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'}, [Assignment start date] = {">=9/1/2017<=9/30/2017"}>} [Data Source])
Hi Sunny,
I want to add the 3rd expression answer should be as below. attached qvw file
US_GU_RES_OG - Metric Name | Sep-17 | Oct-17 | Nov-17 | Dec-17 | FY18 Average |
# of New Cx Contractors | 45 | 37 | 24 | 18 | 35 |
US_GU_RES_OG - Metric Name | Sep-17 | Oct-17 | Nov-17 | Dec-17 | FY18 Average |
# of New Cx Contractors | 45 | 37 | 24 | 18 | 35 |
I want the Assignment start date to be dynamic column
Sep-17 should be[Assignment start date] = {">=9/1/2017<=9/30/2017"}
Oct-17 should be[Assignment start date] = {">=10/1/2017<=10/31/2017"}
Nov-17 should be[Assignment start date] = {">=11/1/2017<=11/30/2017"}
And also can i add FY18 Average column as shown is table
Count({<[Data Source] = {'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'}, [Assignment start date] = {">=9/1/2017<=9/30/2017"}>} [Data Source])
Regard,
Savithri
Set analysis won't work... try this
Count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'}>}
If([Assignment Start date] >=MonthStart(Date#([Current Month/Year], 'MMM, YYYY')) and [Assignment Start date] <= MonthEnd(Date#([Current Month/Year], 'MMM, YYYY')), [Data Source]))
Hi Sunny,
Wow great How can we add one more column as Average FY18 to the entire table
US_GU_RES_OG - Metric Name | Sep-17 | Oct-17 | Nov-17 | Dec-17 | FY18 Average |
Total Cx Labor Based Spend | $8,058,084 | $5,651,602 | $6,261,427 | $7,318,280 | $6,657,038 |
Total Cx Labor Based Spend (millions) | $8 | $6 | $6 | $7 | $7 |
Cx PSL Spend* | $4,947,535 | $3,468,309 | $3,968,075 | $4,823,169 | $4,127,973 |
Cx PSL Spend* (millions) | $5 | $3 | $4 | $5 | $4 |
% Cx PSL Spend | 61% | 61% | 63% | 66% | 62% |
# Unique PSL Suppliers | 15 | 15 | 16 | 16 | 16 |
# of Active NA Cx Managed Contractors | 456 | 475 | 468 | 436 | 466 |
# of Active Direct Sourcing Contractors ** | 29 | 26 | 25 | 25 | 27 |
# of Active NA Cx Enabled Contractors | 64 | 62 | 58 | 57 | 61 |
# of Active Cx Managed Non-Chargeable Contractors | 39 | 39 | 38 | 37 | 39 |
# of Active Cx Managed Non-Chargeable BD Contractors | 13 | 11 | 10 | 12 | 11 |
# of High Cost Contractors (Bill Rate > $150/hr.) | 46 | 47 | 50 | 45 | 48 |
% High Cost Contractors | 10% | 10% | 11% | 10% | 10% |
# of Long Tenure Contractors (>2 yrs.) *** | 62 | 49 | 51 | 64 | 54 |
% Long Tenure Contractors | 14% | 10% | 11% | 15% | 12% |
# of New Cx Contractors | 45 | 37 | 24 | 18 | 35 |
Hi Sunny,
Using the below expression Sept-17 is showing as 0 rest of the months are fine. Can you please help
Count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'}>}
If([Assignment Start date] >=MonthStart(Date#([Current Month/Year], 'MMM, YYYY')) and [Assignment Start date] <= MonthEnd(Date#([Current Month/Year], 'MMM, YYYY')), [Data Source]))
Regards,
Savithri