Create Bar Chart column color for different condition

[Rahmat Khairi]

Hello all,

I am still unfamiliar with the syntax for the column color expression in spotfire and would need some help.

Using election result dataset as an example, I would like to create Bar Chart color such a way that if a particular state (example Alabama) has more county under Democrat than Republican, that state will show blue in the bar chart and vise versa red if the state has more Republican. How would i write the expression in the color by option. The columns for the dataset will be as follow: State, County, Party. In the end i would like to get a Bar Chart that show different state with different color that represent either Democrat or Republican. I have attach a screenshot of the barchart and the dataset. So instead of stacked bar chart, i want it to show for each state the color of the party that has more county. In the dataset, under party column, it has either democrat or republican variable.

 

I tried CASE WHEN Count([party]) = "Democrat" > Count([party]) = "Republican" THEN1 ELSE2 END.

[Kirsten Smith (she/her)]

Try something like: MostCommon([Party]) OVER ([state]), assuming there is just one line for each county in the state.

[Rahmat Khairi]

Hey KirstenS,

Thanks for the prompt reply. I have tried your suggestion but instead of showing the party that the state has more votes on, it only shows republican and those states with no republican at all shows democrat. I have attached a screenshot of it below. I think maybe need to use 'if condition...' in the expression. Kindly assist.

Thanks

[Gaia Paolini]

can you upload the dataset

[Rahmat Khairi]

Hey,

 

I am edit the question and include in the dataset. Please assist my queries. Thanks

 

 

[Gaia Paolini]

Try this for the colour by expression:

 

Where

Sum([votes]) over ([state],[party])

generates the vote count per state and party.

ValueForMax(...) over [state]

takes the value of the [party] with the maximum vote count per state.

The natural colour order is blue first but it is a matter of changing the colours to what you want.

[Rahmat Khairi]

Wow it works. Thanks very much!