Expressions

[savithri_n_s]

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

3.

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

[shilpan]

Right parenthesis is missing:

count(

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

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

        DISTINCT([Client - Normalized])

       >

    }

      [Client - Normalized]

     )

[savithri_n_s]

Hi Patel,

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

Can you please help?

Regards,

Savithri

[shilpan]

count

    (

     {

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

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

       >

    }

     DISTINCT([Client - Normalized])

     )

[savithri_n_s]

Thanks Patel

Can you please tell me what is wrong here

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

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

[shilpan]

What is the object id and expression label?

[savithri_n_s]

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

[shilpan]

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

[savithri_n_s]

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

[savithri_n_s]

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