- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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:
Here is the documentation for creating a custom category:
Does that help?
If the scoring ranges change according to the value of KPIID, then things get a little more complicated.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, It changes according to the KPIID.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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' )