Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I need to show changes in contract Ratings. Not only how many Ratings in category are new, but also how many Ratings are improved and how many are worser. While I have no problem to get number of new atings, I struggle with the improved and worser once (when I use a table with Dimension Rating AND contract that works as well). I want a Chart only with Dimension Ratings and number of improved/worser contracts.
As I am not able to upload my short sample while I am on customer site, I copy what I did:
I use this Inline table:
TEST:
Load * inline [
Date, contract, rating, ratid
01.05.2016, 123, AAA, 1
01.05.2016, 345, A+, 5
01.05.2016, 456, BBB+, 8
01.05.2016, 678, AA-, 4
01.06.2016, 123, AAA, 1
01.06.2016, 345, AA-, 4
01.06.2016, 456, BBB, 9
01.06.2016, 678, AA-, 4
];
left join(TEST)
load
contract,
rating,
contract&rating as contractrating
Resident TEST;
When I select June I get this result (one new Rating for AA- - better -and one new for BBB -worser).
I use this in Expression:
=count(distinct {<contract*= P({<Date={'$(=addmonths(Date,-1))'}>}contract),
contractrating-= P({<Date={'$(=addmonths(Date,-1))'}>}contractrating)>}contractrating)
// first get common contracts (is necessary to exclude new contracts)
// second we extract those contracts which are same (contract and Rating)
// result in difference
How is it possible to identify the number of improved or worser contracts?
I know that I can do it by calculating in script. Any other Chance?
Rudolf,
Is this what you need?
contract | Date | rating | Change |
---|---|---|---|
123 | 01.05.2016 | AAA | |
123 | 01.06.2016 | AAA | No change |
345 | 01.05.2016 | A+ | |
345 | 01.06.2016 | AA- | Up |
456 | 01.05.2016 | BBB+ | |
456 | 01.06.2016 | BBB | Down |
678 | 01.05.2016 | AA- | |
678 | 01.06.2016 | AA- | No change |
There is one dimension used to order the table which is hidden from view, contract, Date and rating are entered as if expressions which should be okay as they will be unique for each value of the calculated dimension. The final column's expression is hopefully what you want.
=contract & '|' & Date | contract | Date | rating | if(above(contract) = contract, Pick(2+Sign(Above(ratid) -ratid),'Down', 'No change','Up')) |
---|---|---|---|---|
123|01.05.2016 | 123 | 01.05.2016 | AAA | |
123|01.06.2016 | 123 | 01.06.2016 | AAA | No change |
345|01.05.2016 | 345 | 01.05.2016 | A+ | |
345|01.06.2016 | 345 | 01.06.2016 | AA- | Up |
456|01.05.2016 | 456 | 01.05.2016 | BBB+ | |
456|01.06.2016 | 456 | 01.06.2016 | BBB | Down |
678|01.05.2016 | 678 | 01.05.2016 | AA- | |
678|01.06.2016 | 678 | 01.06.2016 | AA- | No change |
cheers
Andrew
unfortunately this is not the solution for my customer
as we have more than 100.000 datarows per month, the result would be a huge table which is not practicable
even if you only take the contract where the Rating had been changed it is still a large number
The customer wants a table/Chart like this
Rating Up Down
AAA 10 5
AA 3 8
A+ 12 15
now he can select the rating and with those minor data (but still in the thousands) he can have a look at table
I think that I end up with calculating in script
Hi Rudolf,
The best way is calculate on script:
left join(TEST)
load
contract,
Date,
rating,
contract&rating as contractrating,
IF(contract = Previous(contract),
IF(ratid<Previous(ratid),'Up',
if(ratid > Previous(ratid),'Down','No Change')),'Inclusion') as Result
Resident TEST
ORDER BY contract, Date;
Regards!