I've created a categorization band for total investments, but I'm encountering an issue where the 8th band ("Above 6m up to 10m") is not displaying, e

[LoKi_asterix]

I've created a categorization band for amount, but I'm encountering an issue where the 8th band ("Above 6m up to 10m") is not displaying, even after adding Sum({1} 0) to the expression.

Here’s the expression I’m using:

IF(Amount<= 100000, DUAL('Up to 100k',1),
IF(Amount> 100000 and Amount<= 300000, DUAL('Above 100k up to 300k',2),
IF(Amount> 300000 and Amount<= 500000, DUAL('Above 300k up to 500k',3),
IF(Amount> 500000 and Amount<= 1000000, DUAL('Above 500k up to 1m',4),
IF(Amount> 1000000 and Amount<= 2000000, DUAL('Above 1m up to 2m',5),
IF(Ammount> 2000000 and Amount<= 4000000, DUAL('Above 2m up to 4m',6),
IF(Amount> 4000000 and Amount<= 6000000, DUAL('Above 4m up to 6m',7),
IF(Amount> 6000000 and Amount<= 10000000, DUAL('Above 6m up to 10m',8),
DUAL('Above 10m',9)
))))))))
AS [Bands];

[QFabian]

Hi @LoKi_asterix , first, there is an additional m in the 6th band Amount file --> Ammount

And if is not that, check this example that i created :

///a few rows, you can find more in the txt with the full data example

Data:
Load * INLINE [
Id, Amount
1 , 41094104
2 , 53558154
3 , 77401058

];

Band_Range:

Load * INLINE [

Min, Max, Band

       1,   100000, Up to 100k

  100000,   300000, Above 100k up to 300k

  300000,   500000, Above 300k up to 500k

  500000,  1000000, Above 500k up to 1m

 1000000,  2000000, Above 1m up to 2m

 2000000,  4000000, Above 2m up to 4m

 4000000,  6000000, Above 4m up to 6m

 6000000,  9999999, Above 6m up to 10m

10000000, 99999999, Above 10m

];

//intervalmatch to match ranges with amounts

Inner Join IntervalMatch ( Amount ) 

LOAD Min, Max

Resident Band_Range;

Left Join (Data)

Load

Amount,

Band

Resident Band_Range;

drop table Band_Range;

left join (Data)

Load * INLINE [

Band, Band_Number

Up to 100k, 1

Above 100k up to 300k, 2

Above 300k up to 500k, 3

Above 500k up to 1m, 4

Above 1m up to 2m, 5

Above 2m up to 4m, 6

Above 4m up to 6m, 7

Above 6m up to 10m, 8

Above 10m, 9

];

exit script;

[marcus_sommer]

There may simply no values within this range.

If there are really values then remove all selections and use this field as dimension with count() expression or a static 1 within a new object. After that you could reduce the data-set with the wanted selections again and the next step may adding more complexity to the object. When the field-value unexpectedly disappeared you found the trigger and probably also the cause behind it.