I’m currently creating content for a new training class to show users how to use Need4Viz navigation widgets and to integrate with built-in Web Intelligence features like filtering and element linking. I needed a use-case for a double slider.
I decided to use eFashion and create a holiday gift finder that allows users to specify the top and bottom of their budget which in turn filters a table of eFashion items and their MSRP (manufacturer’s suggested retail price).
The only thing more daunting than the number of individual SKUs (stock-keeping units) in eFashion is the variety of colors. Since there were too many colors for a dashboard-sized filter widget, I opted to group the colors into families. Just like your data, eFashion has data quality issues, misspelled color names, and color names that even the internet wasn’t sure what color family it should belong to. There were also issues with case.
I overcame the case issues by wrapping the color values with the Lower() function. And I was able to overlook a lot of data quality sins by using the Pos() function and in some cases only looking for a portion of a color name. Here’s what I came up with.
=If (Pos(Lower([Category]); "jewelry")) >0 Then "Jewelry"
ElseIf (Pos(Lower([Color]); "striped")) >0 Or (Pos(Lower([SKU desc]); "striped")) >0 Then "Striped"
ElseIf (Pos(Lower([Color]); "print")) >0 Or (Pos(Lower([Color]); "gaelic")) >0 Or (Pos(Lower([Color]); "jungle")) >0 Then "Print"
ElseIf (Pos(Lower([Color]); "white")) >0 Or (Pos(Lower([Color]); "porcelain")) >0 Then "White"
ElseIf (Pos(Lower([Color]); "anis")) >0 Or (Pos(Lower([Color]); "black")) >0 Or (Pos(Lower([Color]); "ebony")) >0 Or (Pos(Lower([Color]); "ink")) >0 Or (Pos(Lower([Color]); "marble")) >0 Or (Pos(Lower([Color]); "opal")) >0 Then "Black"
ElseIf (Pos(Lower([Color]); "grey")) >0 Or (Pos(Lower([Color]); "gray")) >0 Or (Pos(Lower([Color]); "ceramic")) >0 Then "Grey"
ElseIf (Pos(Lower([Color]); "red")) >0 Or (Pos(Lower([Color]); "raspberry")) >0 Or (Pos(Lower([Color]); "rougue")) >0 Or (Pos(Lower([Color]); "bordeaux")) >0 Or (Pos(Lower([Color]); "strawberry")) >0 Or (Pos(Lower([Color]); "tomato")) >0 Or (Pos(Lower([Color]); "garnet")) >0 Or (Pos(Lower([Color]); "pomegranate")) >0 Or (Pos(Lower([Color]); "ruby")) >0 Or (Pos(Lower([Color]); "grenadine")) >0 Then "Red"
ElseIf (Pos(Lower([Color]); "pink")) >0 Or (Pos(Lower([Color]); "forget-me-not")) >0 Or (Pos(Lower([Color]); "rose")) >0 Or (Pos(Lower([Color]); "mauvre")) >0 Or (Pos(Lower([Color]); "visine")) >0 Then "Pink"
ElseIf (Pos(Lower([Color]); "orange")) >0 Or (Pos(Lower([Color]); "mandarin")) >0 Or (Pos(Lower([Color]); "paprika")) >0 Or (Pos(Lower([Color]); "saffron")) >0 Or (Pos(Lower([Color]); "rusty")) >0 Or (Pos(Lower([Color]); "pumpkin")) >0 Or (Pos(Lower([Color]); "melon")) >0 Or (Pos(Lower([Color]); "maple")) >0 Then "Orange"
ElseIf (Pos(Lower([Color]); "yellow")) >0 Or (Pos(Lower([Color]); "butter")) >0 Or (Pos(Lower([Color]); "gold")) >0 Or (Pos(Lower([Color]); "corn")) >0 Or (Pos(Lower([Color]); "honey")) >0 Or (Pos(Lower([Color]); "muscadet")) >0 Then "Yellow"
ElseIf (Pos(Lower([Color]); "absinthe")) >0 Or (Pos(Lower([Color]); "algae")) >0 Or (Pos(Lower([Color]); "green")) >0 Or (Pos(Lower([Color]); "emerald")) >0 Or (Pos(Lower([Color]); "grass")) >0 Then "Green"
ElseIf (Pos(Lower([Color]); "blue")) >0 Or (Pos(Lower([Color]); "indigo")) >0 Or (Pos(Lower([Color]); "marine")) >0 Or (Pos(Lower([Color]); "turquoise")) >0 Or (Pos(Lower([Color]); "water")) >0 Or (Pos(Lower([Color]); "outremer")) >0 Then "Blue"
ElseIf (Pos(Lower([Color]); "purple")) >0 Or (Pos(Lower([Color]); "prune")) >0 Or (Pos(Lower([Color]); "eggplant")) >0 Then "Purple"
ElseIf (Pos(Lower([Color]); "chocolate")) >0 Or (Pos(Lower([Color]); "coffee")) >0 Or (Pos(Lower([Color]); "brown")) >0 Then "Brown"
ElseIf (Pos(Lower([Color]); "beige")) >0 Or (Pos(Lower([Color]); "buff")) >0 Or (Pos(Lower([Color]); "clay")) >0 Or (Pos(Lower([Color]); "caramel")) >0 Or (Pos(Lower([Color]); "cognac")) >0 Or (Pos(Lower([Color]); "kaki")) >0 Or (Pos(Lower([Color]); "ecru")) >0 Or (Pos(Lower([Color]); "ivory")) >0 Or (Pos(Lower([Color]); "natural")) >0 Or (Pos(Lower([Color]); "ochre")) >0 Or (Pos(Lower([Color]); "earth")) >0 Or (Pos(Lower([Color]); "beach")) >0 Or (Pos(Lower([Color]); "linen")) >0 Or (Pos(Lower([Color]); "tawny")) >0 Or (Pos(Lower([Color]); "vanilla")) >0 Or (Pos(Lower([Color]); "stone")) >0 Or (Pos(Lower([Color]); "bamboo")) >0 Or (Pos(Lower([Color]); "sesame")) >0 Or (Pos(Lower([Color]); "ficelle")) >0 Then "Earth Tone"
Else "Unknown"
Notice that I used some broad brushstrokes to deal with the mayhem of colors in the Jewelry category and handled striped SKUs and color names early in the logic. After that, it’s just a lot of cutting and pasting with the Not-So-Smart™ variable editor in Web Intelligence 4.3. I am hoping that an updated variable editor in BI 2025 will be just a bit smarter and lose some of its “je ne sais quoi” qualities. Ideally, color family is an idea that could have been introduced upstream and handled in the eFashion data warehouse. But it’s nice to know that the Web Intelligence function library is robust enough (and gains new functions with each release) to handle lots of scenarios.
So what do you think? I’ve reduced the number of colors from 209 down to just 16 color families. I’m not sure if radio buttons or checkboxes are the best choice for that many values and I may end up with a drop-down list or other type of navigation control. I also need some additional time to create a red-and-green holiday gift color palette and make the dashboard more festive. So, you may soon see a completed dashboard on the Need4Viz website or featured in a webinar. Until then.
