Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
craig157
Creator II
Creator II

Negative Points for If(IsNull(...

Hi All,

I am trying to create a report that will hopefully show which of our customer records will need various improvements.

Essentially I want to create a Ranking Table.

So I have setup multiple variables as below:

vMaxRankingPoints = 10

vNullCompanyName = -1

vNullAddressPostcode = -1

vNullCounty = -1

vNullTelephoneNumber = -1

And so on and so on.

I've set these up as Variables as in future I would like to be able to change the values for putting a push on getting certain fields improved.

So the end goal is to find out how many records have landed within certain 'Ranking Points' so we can investigate why they have been neglected and such.

Each field has a relevant 'ID' which I want to allocate a ranking too - and then check the fields for Null values and remove Ranking Points accordingly.

I've tried doing like this expression below but had no joy and not sure what type of Function I should be looking for:

aggr(cOrgID > 0, vMaxRankingPoints)

AND If(IsNull([CompanyName]),vNullCompanyName)

AND If(IsNull([Address1]),vNullAddress1)

AND If(IsNull([AddressCounty]),vNullAddressCounty)

AND If(IsNull([AddressPostcode]),vNullAddressPostcode)

AND If(IsNull([AddressTown]),vNullAddressTown)

AND If(IsNull([Tel1]),vNullTelephone)

AND If(IsNull([modeled_turnover_band_desc]),vNullTurnover)

AND If(IsNull([WWW]),vNullWebsite)

Hopefully this would give me an end result per ID like: ID = 003449002 Ranking = 7

The goal after this would then be to group these so I could have a count of how many companies per Ranking Point.

Sorry if I've got carried away but just trying to cover all basis.

Cheers

1 Solution

Accepted Solutions
sunny_talwar

I think you are looking to add these points and the way it is structured right now, it won't add... try something like this

RangeSum(

If(cOrgID > 0, vMaxRankingPoints),

If(IsNull([CompanyName]), vNullCompanyName),

If(IsNull([Address1]),vNullAddress1),

....

)

View solution in original post

2 Replies
sunny_talwar

I think you are looking to add these points and the way it is structured right now, it won't add... try something like this

RangeSum(

If(cOrgID > 0, vMaxRankingPoints),

If(IsNull([CompanyName]), vNullCompanyName),

If(IsNull([Address1]),vNullAddress1),

....

)

craig157
Creator II
Creator II
Author

Hi Sunny!

That worked absolutely perfect!

Thanks for your help on this