topic Text colour based on expression not working correctly. in QlikView

Text colour based on expression not working correctly.

lukegilligan — Sat, 04 Nov 2017 11:41:22 GMT

I have a SUM(TOTAL) field that I am wanting to colour code 3 ways, Green if it matches new Rent, Orange if it matches Old Rent and Red if it matches neither.

The code I am using for this is as follows:

=if(SUM(TOTAL <CRMOrganisation.code> familyUnitRent)=newRebatedRentPerWeek,rgb(46, 204, 113)

,if(SUM(TOTAL <CRMOrganisation.code> familyUnitRent)=rebatedRentPerWeek,rgb(230, 126, 34),rgb(192, 57, 43))

)

Basically it takes everyone in the households (CRMOrganisation.code) pre-calculated portion of the rent (familyUnitRent), adds it together and determines if it matches the new rent, the old rent or none of the above.

For the majority of the rows its working no issues, but I cant find any kind of similarity between the ones that are failing and when I go back to my source system and manually calculate it all, it should be showing as green.

The only thing I can think might be happening is that some members of the household are filtered out at the clinkCRNNumber dimension by an IF statement, but I have tried removing this and the problem still persists.

Any help would be much appreciated.

Re: Text colour based on expression not working correctly.

petter — Sat, 04 Nov 2017 14:01:18 GMT

Can you be more specific as to how it is "failing" - and possibly the rows that you think are failing...?

Re: Text colour based on expression not working correctly.

lukegilligan — Sat, 04 Nov 2017 20:58:05 GMT

Hi Peter, basically any of the Individual Amount column that has gone red is wrong.

In the image below, you can see both people are part of the one CRMOrganisation.code group 126204.99031267. This is their household with a fortnightly Rent Total of $713.10 per fortnight.

We have software on the backend that determines how much each person within the household is supposed to pay (this is based on their income, and irrelevant to the errors I am seeing) and this is their Individual Amount.

The SUM of all of the Individual Amount's that share a CRMOrganisation.code should equal the fortnightly Rent Total and go green to show that they do equal the correct amount.

As in this example though Customer 211358462C's amount is 373.08 and Customer 433783222V's amount is 340.02 which when added together gives 713.10 the same as the fortnightly Rent Total which should turn it gree, however it is red.

Re: Text colour based on expression not working correctly.

lukegilligan — Sat, 04 Nov 2017 21:20:35 GMT

I might have some more clues as to why this isn't working.

Each of the Individual Amounts on their own do not equal Rent Total for those that are not working, eg in the example above Customer 211358462C's amount is 373.08 and Customer 433783222V's amount is 340.02, but the Rent Total per week is 356.55 (which neither equal the totals). This leads me to believe that somehow my SUM(TOTAL) based on the CRMOrganisation.code is not working correctly.

Does this help with where I have gone wrong at all?

Thanks

Re: Text colour based on expression not working correctly.

lukegilligan — Wed, 08 Nov 2017 01:55:23 GMT

Hi Petter, any thoughts?