Pivot Table subtotals background color with colormix

[oehmemat]

Hi all,

I think I read all related post in the forum but I couldn't find a solution:

I'm trying to use the colormix2 function for coloring the background of the subtotals of a pivot table. I checked all the formulas, and tried a lot.

This works with the formulas (Value / Threshold) I use in the colormix function:

if(rowno()=0,

if(Value>Threshold,red(),green())

)

But this isn't working:

if(rowno()=0,

<colormix2Statement>

)

Im using the wizard to generate the colormix statement.

Is it even supposed to work?

[Anil_Babu_Samineni]

Please post full Wizard formula which is not working?

[oehmemat]

Hi Anil,

thank you for your response.

Here is the formula:

ColorMix2 (
if(aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)<median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)),
	//then
	-Sqrt(-(aggr(sum( RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)
	-median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)))
	/(median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID))
	-RangeMin (top(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID),1,NoOfRows(total)))))
	
	//else
	,Sqrt((aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)
	-median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)))
	/(RangeMax (top(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID),1,NoOfRows(total)))
	-median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID))))),
//Colormix High,Low,In Between
 ARGB(255, 255, 0, 0), ARGB(64, 255, 0, 0), ARGB(128, 255, 0, 0))

I think the problem lies in the RangeMax () function, it uses NoOfRows(total), but it returns no value as the subtotal rows doesn't count as a regular row.

I fiddled around with the rank() function, but had no luck as the ranks aren't displayed in the subtotals either

[Brett_Bleess]

The expression is pretty large, the best I can offer at the moment are the Help links, so you can check your syntax to be sure that is correct.  I would likely do something much more basic for starters to be sure that it works as you expect that way, then try to increase the complexity, that way you should be able to see where it stops working...

Color Functions:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

ColorMix Wizard:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Colormix_Wizard...

Sorry I do not have anything better for you, but hopefully this will help you work back through things to check all your syntax etc.

Regards,
Brett

[Anil_Babu_Samineni]

As Mentioned by @Brett_Bleess  It is quite long. Only I can think here is

if(aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)<median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)),

ARGB(255, 255, 0, 0), ARGB(64, 255, 0, 0))

Or

ColorMix2(Sum(aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID)<median(total aggr(sum(RecipesInput.AskPrice*RecipesInput.Amount),Colonies.Planet,Buildings.ID,RecipesOutput.CommodityID,RecipesOutput.Amount,Recipes.ID,Recipes.Duration,RecipesInput.ID,RecipesInput.CommodityID, RecipesInput.CommodityID))), ARGB(255, 255, 0, 0), ARGB(64, 255, 0, 0), ARGB(128, 255, 0, 0))