topic Re: Expressions in QlikView

Expressions

savithri_n_s — Fri, 02 Mar 2018 11:56:56 GMT

Hi Sunny

I am unable to get the value in Sheet 5 of qvw file

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

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

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 12:13:57 GMT

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 12:15:25 GMT

Which expression are you talking about for your second requirement above?

Re: Expressions

savithri_n_s — Fri, 02 Mar 2018 12:26:36 GMT

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 12:34:55 GMT

Try this for second

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 12:37:41 GMT

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

Re: Expressions

savithri_n_s — Fri, 02 Mar 2018 13:00:53 GMT

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 13:08:47 GMT

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

Re: Expressions

savithri_n_s — Fri, 02 Mar 2018 13:13:34 GMT

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

Re: Expressions

savithri_n_s — Fri, 02 Mar 2018 13:31:09 GMT

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

Re: Expressions

sunny_talwar — Fri, 02 Mar 2018 13:39:30 GMT

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