How to get color for irregular intervals of sales using interval Match ?

[shekhar_analyti]

Hi All ,

How to get color for irregular intervals of sales using interval Match ?

Below is my Data Patter and Ranges

    

I have to Show sales value in Text Object .

Thanks & Regards

Shekar

[shekhar_analyti]

antoniotiman‌ ... help please

[hari8088]

See the below script..............

Sales:

LOAD * Inline [

Region,sales

Central,56

Hinerland,72

Bay Area,88

Hills,29

Rakine,78

Tropics,70

Forest,92

];

Salesnew:

LOAD Region,

num(sales) as sales,

'' as flag

Resident Sales;

DROP Table Sales;

Data:

Load * Inline [

Range,colour

13-30,Deep red

40-50,Light red

55-60,White

70-80,Orange

80-90,Light green

93-100,Dark green

];

Range:

LOAD

colour,

num(SubField(Range,'-',1)) as Minrange,

num(SubField(Range,'-',-1)) as Maxrange

Resident Data;

DROP Table Data;

Left Join(Salesnew)

IntervalMatch(sales)

LOAD Minrange,Maxrange Resident Range;

DROP Table Range;

Left Join(Salesnew)

LOAD *

Resident Range;

DROP Table Range;

[hari8088]

See the attached qvw...

[effinty2112]

Hi Shektar,

Try:

[Colour Code]:

Load

Colour,

SubField(Range,'-',1) as From,

SubField(Range,'-',2) as To;

LOAD Range,

     Colour

FROM

[Color Code.xlsx]

(ooxml, embedded labels, table is Range);

Sales:

LOAD Region,

     Sales

FROM

[Color Code.xlsx]

(ooxml, embedded labels, table is Sales);

Link: IntervalMatch(Sales) LOAD From, To Resident [Colour Code];

Don't worry about the synthetic key, it's expected and for an intervalmatch it's optimal. you can remove it if you want by adding more lines to your script.

This is the result:

Region	Colour	sum(Sales)
Hills	Deep Red	29
Central	White	56
Tropics	Orange	70
Hinerland	Orange	72
Rakine	Orange	78
Bay Area	Light Green	88
Forest		92

No colour for Forest be cause of the gap in the colour code ranges.

Regards

Andrew

[swuehl]

Are your sales values calculated in the frontend using an aggregation and can't be precalculated in the script?

[shekhar_analyti]

Dear Krishna ,

Actually i wanted the color of sales (displayed in Text Box) value to be in colour based on Range .

I am sorry if I could not clear my requirement by putting in proper words .

I requirement is something like this

Basically i wanted the color of sales value based on the Range  .

Thanks & Regards

Shekar

[shekhar_analyti]

Hi Stefan ,

Yes , the sales values are getting calculated using aggregation based on particular set ..

Regards

Shekar

[shekhar_analyti]

Thank you Andrew . But My requirement was different  .

@

[hari8088 · Accepted solution]

Hi shekar,

See the attached qvw is this what you want.