SAS VA

[megha_singh89]

Raw Data
KPIID	Quarter	Total	Meet	Date	Meet%
1	Q3-2015	6278	6102	Sep-2015	97%
1	Q3-2015	3240	2294	Sep-2015	71%
2	Q3-2015	5479	3819	Sep-2015	70%
2	Q4-2015	7712	2534	Oct-2015	33%
2	Q4-2015	4537	3489	Oct-2015	77%
1	Q4-2015	2003	1823	Nov-2015	91%
1	Q4-2015	3129	2519	Nov-2015	81%
2	Q4-2015	7238	392	Dec-2015	5%
2	Q4-2015	6838	3367	Dec-2015	49%

Meet%= Meet/Total

	Score Table
	KPIID	From	To	Score
	1	0.00%	10.00%	1
	1	10.10%	40.00%	2
	1	41.00%	80.00%	3
	1	81.00%	90.00%	4
	1	91.00%	100.00%	5
	2	0.00%	10.00%	1
	2	10.10%	40.00%	2
	2	41.00%	80.00%	3
	2	81.00%	90.00%	4
	2	91.00%	100.00%	5
	3	0.00%	10.00%	1
	3	10.10%	40.00%	2
	3	41.00%	80.00%	3
	3	81.00%	90.00%	4
	3	91.00%	100.00%	5
	4	0.00%	10.00%	1
	4	10.10%	40.00%	2
	4	41.00%	80.00%	3
	4	81.00%	90.00%	4
	4	91.00%	100.00%	5

We need to Match the Meet% calculated for each record in the Raw Data table to the Score table and then assign a score depending upon “From” & “To” range and KPIID.

Example:

If we have to generate Score on monthly view then it should aggregate total and meet number then produce a meet% and with this meet% we have to find the score from Score table:

KPI ID	Month	Total	Meet	Meet%	Score
1	Sep-2015	14997	12215	81.45%	4

[Sam_SAS]

Hello,

I believe that what you want should be possible in VA.

1. Create a calculated item to create your Meet% column.

2. Create a custom category based on Meet% to set the scoring values.

The scoring values should be assigned appropriately according to the aggregated values for Meet%.

Here is the documentation for creating calculated data items:

https://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n0oqwv06on7r0fn1qg8c...

Here is the documentation for creating a custom category:

https://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0uroboxihl12on1pzzk...

Does that help?

If the scoring ranges change according to the value of KPIID, then things get a little more complicated.

[megha_singh89]

Yes, It changes according to the KPIID.

[Sam_SAS]

I believe you should still be able to make this work.

For each KPIID value, create a separate custom category to define the value range labels (scores).

Create a calculated item where the expression is like this:

IF ( 'KPIID'n = '1' )
RETURN 'KPI1'n
ELSE ( 
  IF ( 'KPIID'n = '2' )
  RETURN 'KPI2'n
  ELSE 'KPI3' )