2 Replies Latest reply: Jul 20, 2017 8:14 AM by Craig Bannerman RSS

    Negative Points for If(IsNull(...

    Craig Bannerman

      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