I have 4 columns that calculate an employees commission based on if they hit that category. I have an iif statement working that only calculates if all are hit and if some are hit. What i need to do is have it so that if 1,2,3,4 are hit then column 5 will show "4" and if column 2,4 are hit then show "2". This can be in any sequence. So some may be only column 4,1 are true or column 2,3,4 are true which would then equal 3.
I have tried the below code:
=IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND
ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70 AND
ReportItems!tmppercent.Value > .7499,"4", IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND
ReportItems!gp.Value > ReportItems!gp70goal.Value AND
ReportItems!accessorygpperbox.Value > 70,"3",IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value AND
ReportItems!gp.Value > ReportItems!gp70goal.Value,"2",IIf(ReportItems!gp.Value > ReportItems!gpgoal.Value,"1","0")
My SQL code
WITH MEMBER [Measures].[DateYMDUniqueName] AS IIF (
IsEmpty([Measures].[Quantity])
,Nothing
,[Date YMD].CURRENTMEMBER.UNIQUENAME
)
MEMBER [Measures].[DateUniqueName] AS IIF (
IsEmpty([Measures].[Quantity])
,Nothing
,[Date].CURRENTMEMBER.UNIQUENAME
) MEMBER [Measures].[ParentUniqueName] AS IIF (IsEmpty([Measures].[Quantity])
,Null
,[Categories and Products].CURRENTMEMBER.PARENT_UNIQUE_NAME
) MEMBER [Measures].[UniqueName] AS IIF (IsEmpty([Measures].[Quantity])
,Null
,[Categories and Products].CURRENTMEMBER.UNIQUENAME
) MEMBER [Measures].[Level] AS IIF (IsEmpty([Measures].[Quantity])
,Null
,[Categories and Products].CurrentMember.Level.Ordinal
) MEMBER [Measures].[Accessory Value] AS ([Measures].[Profit], [Performance Groups Name].&[155]) MEMBER [Measures].[Insurance Value] AS ([Measures].[Profit], [Performance Groups Name].&[5]) MEMBER [Measures].[Total GP per Device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[36]) MEMBER [Measures].[Total Accessory GP Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[3]) MEMBER [Measures].[Total Insurance GP Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[5]) MEMBER [Measures].[Avg Device GP Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[37]) MEMBER [Measures].[Accessory Dollar per Device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[33]) MEMBER [Measures].[Accessory Qty Goal] AS ([Measures].[Employee Target], [Performance Groups Name].&[3]) MEMBER [Measures].[Insurance Dollar per device Goal] AS ([Measures].[Employee Profit Target], [Performance Groups Name].&[38]) MEMBER [Measures].[Insurance Percent Goal] AS .60 MEMBER [Measures].[GP_Value] AS ([Measures].[Profit], [Performance Groups Name].&[157]) MEMBER [Measures].[GP_Goal] AS [Measures].[Employee Profit Target] MEMBER [Measures].[Activations_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[197]) MEMBER [Measures].[Activations_Goal] AS ([Performance Groups Name].&[197],[Measures].[Employee Target]) MEMBER [Measures].[Upgrades_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[147]) MEMBER [Measures].[Upgrades_Goal] AS ([Performance Groups Name].&[147],[Measures].[Employee Target])
MEMBER [Measures].[Total Devices Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[149]) MEMBER [Measures].[New_Strategic_Devices_Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[151]) MEMBER [Measures].[Accessory Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[155]) MEMBER [Measures].[Insurance Qty] AS ([Measures].[Quantity], [Performance Groups Name].&[174]) MEMBER [Measures].[Total Devices Goal] AS ([Measures].[Employee Target], [Performance Groups Name].&[25]) MEMBER [Measures].[New_Strategic_Devices_Goal] AS ([Performance Groups Name].&[151],[Measures].[Employee Target]) MEMBER [Measures].[Insurance Qty Goal] AS ([Measures].[Employee Target], [Performance Groups Name].&[5]) MEMBER [Measures].[Total Devices Value] AS ([Measures].[Profit], [Performance Groups Name].&[25]) MEMBER [Measures].[Hit Rate Percent Goal] AS .33 MEMBER [Measures].[New Strategic Percent Goal] AS .1556 MEMBER [Measures].[Accessory Qty per Device Goal] AS 2.5 MEMBER [Measures].[SmartPhone Only] AS ([Measures].[Quantity], [Performance Groups Name].&[43]) MEMBER [Measures].[Accessory Bundles] AS ([Measures].[Quantity], [Performance Groups Name].&[158]) MEMBER [Measures].[New and Upg All but CPE and Prepaid] AS ([Measures].[Quantity], [Performance Groups Name].&[64]) MEMBER [Measures].[New And Smartphone Samsung Postpaid] AS ([Measures].[Quantity], [Performance Groups Name].&[55]) MEMBER [Measures].[New and Upgrade Smartphone Postpaid ] AS ([Measures].[Quantity], [Performance Groups Name].&[43]) MEMBER [Measures].[Samsung Flagship] AS ([Measures].[Quantity], [Performance Groups Name].&[165]) MEMBER [Measures].[Hum Count] AS ([Measures].[Quantity], [Performance Groups Name].&[45]) MEMBER [Measures].[Smartphone Postpaid] AS ([Measures].[Quantity], [Categories and Products].&[101011101619]) MEMBER [Measures].[Accessory GP Per Box] AS ([Performance Groups Name].&[3], [Measures].[Quantity]) MEMBER [Measures].[New Jetpack] AS ([Performance Groups Name].&[162],[Measures].[Quantity]) MEMBER [Measures].[New Tablet] AS ([Performance Groups Name].&[161],[Measures].[Quantity]) MEMBER [Measures].[New Prepaid] AS ([Performance Groups Name].&[164],[Measures].[Quantity]) MEMBER [Measures].[CPE M2M Prepay] AS ([Performance Groups Name].&[170],[Measures].[Quantity]) MEMBER [Measures].[Phobio Trade-In] AS ([Measures].[Quantity], [Performance Groups Name].&[91]) MEMBER [Measures].[HYLA Trade-In] AS ([Measures].[Quantity], [Performance Groups Name].&[92]) MEMBER [Measures].[Trade-In Percentage] AS ([Measures].[Quantity], [Performance Groups Name].&[94]) MEMBER [Measures].[New and Upgrade Total] AS ([Measures].[Quantity], [Performance Groups Name].&[149]) MEMBER [Measures].[Moto Z2 Force] AS ([Performance Groups Name].&[82],[Measures].[Quantity]) MEMBER [Measures].[LG G7] AS ([Performance Groups Name].&[103],[Measures].[Quantity]) MEMBER [Measures].[Sam S9 and S9 Plus] AS ([Performance Groups Name].&[104],[Measures].[Quantity]) MEMBER [Measures].[Gizmo] AS ([Performance Groups Name].&[105],[Measures].[Quantity]) MEMBER [Measures].[Samsung S8 S8Plus and Note8] AS ([Performance Groups Name].&[108],[Measures].[Quantity]) MEMBER [Measures].[LG V30] AS ([Performance Groups Name].&[107],[Measures].[Quantity]) MEMBER [Measures].[Basic Phone] AS ([Performance Groups Name].&[89],[Measures].[Quantity]) MEMBER [Measures].[Smartphones] AS ([Performance Groups Name].&[114],[Measures].[Quantity]) MEMBER [Measures].[Gizmo watch] AS ([Performance Groups Name].&[110],[Measures].[Quantity]) MEMBER [Measures].[Apple Watch] AS ([Performance Groups Name].&[167],[Measures].[Quantity]) MEMBER [Measures].[Samsung Watch] AS ([Performance Groups Name].&[118],[Measures].[Quantity]) MEMBER [Measures].[New HUM] AS ([Measures].[Quantity], [Performance Groups Name].&[181])
MEMBER [Measures].[New HUM X] AS ([Measures].[Quantity], [Performance Groups Name].&[182])
MEMBER [Measures].[HUM Devices] AS ([Measures].[Quantity], [Performance Groups Name].&[153]) MEMBER [Measures].[Postpaid Phones and Tablets] AS ([Measures].[Quantity], [Performance Groups Name].&[140]) MEMBER [Measures].[Setup Fee] AS ([Measures].[Quantity], [Performance Groups Name].&[171]) MEMBER [Measures].[Smartphone Post and Prepaid Sales From Stock] AS ([Measures].[Quantity], [Performance Groups Name].&[90]) MEMBER [Measures].[New and Upgrade Postpaid phones] AS ([Performance Groups Name].&[173],[Measures].[Quantity]) MEMBER [Measures].[Postpaid Phones] AS ([Performance Groups Name].&[173],[Measures].[Quantity]) SELECT NON EMPTY { [Measures].[HYLA Trade-In], [Measures].[Phobio Trade-In], [Measures].[CPE M2M Prepay], [Measures].[New and Upgrade Total], [Measures].[LG G7], [Measures].[Moto Z2 Force], [Measures].[Trade-In Percentage], [Measures].[New Jetpack], [Measures].[New Prepaid], [Measures].[New and Upgrade Smartphone Postpaid ], [Measures].[Samsung Flagship], [Measures].[Postpaid Phones], [Measures].[New And Smartphone Samsung Postpaid], [Measures].[Accessory GP Per Box], [Measures].[New Tablet], [Measures].[Hum Count], [Measures].[Smartphone Postpaid], [Measures].[HUM Devices], [Measures].[Postpaid Phones and Tablets], [Measures].[New HUM], [Measures].[New HUM X], [Measures].[New and Upg All but CPE and Prepaid], [Measures].[New and Upgrade Postpaid phones], [Measures].[Setup Fee], [Measures].[Smartphone Post and Prepaid Sales From Stock], [Measures].[Samsung Watch], [Measures].[Samsung S8 S8Plus and Note8], [Measures].[LG V30], [Measures].[Sam S9 and S9 Plus], [Measures].[Gizmo], [Measures].[Gizmo watch], [Measures].[Apple Watch], [Measures].[Basic Phone], [Measures].[Smartphones], [Measures].[New_Strategic_Devices_Qty], [Measures].[New_Strategic_Devices_Goal], [Measures].[Total Devices Qty], [Measures].[Accessory Qty Goal], [Measures].[Insurance Qty], [Measures].[Insurance Qty Goal], [Measures].[Accessory Qty], [Measures].[Total Devices Goal], [Measures].[GP_Value], [Measures].[GP_Goal], [Measures].[Accessory Bundles], [Measures].[Activations_Goal], [Measures].[Upgrades_Qty], [Measures].[Upgrades_Goal], [Measures].[Activations_Qty], [Measures].[Total Accessory GP Goal], [Measures].[Hit Rate Percent Goal], [Measures].[Total Devices Value], [Measures].[Hours Punched], [Measures].[New Strategic Percent Goal], [Measures].[SmartPhone Only], [Measures].[Accessory Qty per Device Goal], [Measures].[Sales], [Measures].[Insurance Percent Goal], [Measures].[Insurance Value], [Measures].[Total Insurance GP Goal], [Measures].[Accessory Value], [Measures].[Total GP per Device Goal], [Measures].[Insurance Dollar per device Goal], [Measures].[Accessory Dollar per Device Goal], [Measures].[Avg Device GP Goal] } ON COLUMNS, NON EMPTY { ([Locations and Employees].[Employee Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LocationsandEmployees, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateYMD, CONSTRAINED) : STRTOMEMBER(@ToDateYMD, CONSTRAINED) ) ON COLUMNS FROM [Super])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I know im on the right track but maybe someone has a way to do this easier or cleaner.
I think you're going to be a lot better off using a SWITCH statement with this many conditional statements. Each conditional in a SWITCH only has a single value that it can evaluate to so you can set the value only when the condition is true.
=SWITCH(ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70 AND ReportItems!tmppercent.Value > .7499,"4",
ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value AND ReportItems!accessorygpperbox.Value > 70,"3",
ReportItems!gp.Value > ReportItems!gpgoal.Value AND ReportItems!gp.Value > ReportItems!gp70goal.Value,"2",
ReportItems!gp.Value > ReportItems!gpgoal.Value,"1",
true, "0")
This expression basically duplicates your expression, but does it without a need for multiple IIF and the last condition sets the value to zero for any records that don't match the first four conditional statements.