Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

complex calculation

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?

3 Replies
effinty2112
Master
Master

Rudolf,

          Is this what you need?

contract Date rating Change
12301.05.2016AAA 
12301.06.2016AAANo change
34501.05.2016A+ 
34501.06.2016AA-Up
45601.05.2016BBB+ 
45601.06.2016BBBDown
67801.05.2016AA- 
67801.06.2016AA-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.201612301.05.2016AAA 
123|01.06.201612301.06.2016AAANo change
345|01.05.201634501.05.2016A+ 
345|01.06.201634501.06.2016AA-Up
456|01.05.201645601.05.2016BBB+ 
456|01.06.201645601.06.2016BBBDown
678|01.05.201667801.05.2016AA- 
678|01.06.201667801.06.2016AA-No change

cheers

Andrew

Anonymous
Not applicable
Author

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

manucamon
Specialist III
Specialist III

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!