topic Re: complex calculation in QlikView

complex calculation

Anonymous — Wed, 22 Jun 2016 11:00:15 GMT

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?

Re: complex calculation

effinty2112 — Wed, 22 Jun 2016 13:49:50 GMT

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

Re: complex calculation

Anonymous — Wed, 22 Jun 2016 13:58:51 GMT

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

Re: complex calculation

Anonymous — Wed, 22 Jun 2016 14:12:15 GMT

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!