topic Re: Expressions in QlikView

Expressions

savithri_n_s — Fri, 02 Mar 2018 20:11:19 GMT

Hi Sunny,

Thanks for helping me

Can you please tell me what is wrong in the below expressions and also how to add average column in straight table in sheet5 as

shown below (3.)

1. count({<[Data Source] = {'CX Managed','Cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'},DISTINCT([Client - Normalized])>}[Client - Normalized]

2. (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])))

US_GU_RES_OG - Metric Name	Sep-17	Oct-17	Nov-17	Dec-17	FY18 Average
# 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

Re: Expressions

shilpan — Fri, 02 Mar 2018 20:36:30 GMT

Right parenthesis is missing:

count(

{<[Data Source] = {'CX Managed','Cx Managed'},

[Assignment State]= {'Effective','Effective-OnBoard'},

DISTINCT([Client - Normalized])

}

[Client - Normalized]

)

Re: Expressions

savithri_n_s — Sat, 03 Mar 2018 08:28:13 GMT

Hi Patel,

Thanks. However the expression is not giving the result still throws error.

Can you please help?

Regards,

Savithri

Re: Expressions

shilpan — Sat, 03 Mar 2018 18:22:58 GMT

count

(

{

<[Data Source] = {'CX Managed','Cx Managed'},

[Assignment State]= {'Effective','Effective-OnBoard'}

}

DISTINCT([Client - Normalized])

)

Re: Expressions

savithri_n_s — Sat, 03 Mar 2018 18:36:20 GMT

Thanks Patel

Can you please tell me what is wrong here

And also how can we add average FY18 column in Qvw file of Dashboard 5 as shown below in straight table

I have used the expression to derive each row .For example to get Sep-17 to Dec -17 data below expression is used. Now how can i add avg FY18 column?

It would be great if you can help me on this

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

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

Re: Expressions

shilpan — Sat, 03 Mar 2018 18:45:19 GMT

What is the object id and expression label?

Re: Expressions

savithri_n_s — Sat, 03 Mar 2018 18:51:25 GMT

Hi Patel,

I am referring to this screen shot sheet name Dashboard 5 .Example expression label is

# of Active NA Cx Managed Contractors

and expression used is count({<[Data Source]={'CX Managed','cx Managed'}, [Assignment State]= {'Effective','Effective-OnBoard'}>}[Data Source])

All the rows below # of Active NA Cx Managed Contractors are derived expression. I need to add the average for all

Re: Expressions

shilpan — Sat, 03 Mar 2018 18:55:34 GMT

Values are case sensitive so use count({<[Data Source]={''Cx Managed'}

Re: Expressions

savithri_n_s — Sat, 03 Mar 2018 19:05:13 GMT

even then expression

Normal REQs- Average Hourly Bill Rate is showing 0 after using the below expression

((sum({<[Data Source] = {'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'}, [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]))))

Answer should be as below

US_GU_RES_OG - Metric Name	Sep-17	Oct-17	Nov-17	Dec-17	FY18 Average
Normal REQs- Average Hourly Bill Rate	$83.65	$81.30	$80.78	$80.79	$81.91

Re: Expressions

savithri_n_s — Sun, 04 Mar 2018 14:31:47 GMT

Hi Patel,

I was able to figure out thanks for your help

Can you please let me know how to make the Total Cx Labor Based Spend to change dynamically based on each month in straight table. Refer Dashboard 5.Currently same spend is shown cross all months

And also how can we add average(FY18 Average) based these expression in straight table as shown below

Re: Expressions

shilpan — Sun, 04 Mar 2018 23:37:03 GMT

Issue seems to be with your data model. Can you use current month instead of current month/year?

Re: Expressions

savithri_n_s — Mon, 05 Mar 2018 02:06:52 GMT

Hi patel

Thanks I will check.it would be great if you can tell me you to add fy18 average column as shown above?