Conditional Visual Cue in Pivot table based on only one value of a particular dim

[vvira1316]

Hi,

Following table is a Pivot Table in QV App. I've a situation where only Variance row of [Actual / Budget] dimension needs to be represented in Red/Green font based on >0 or <0. I've written statement and tried using it in visual cue (Upper, Normal, Lower, Text) but it keeps all rows in black. I've tested my condition on individual values and it is working as I want for value related to KPI, Actual/Budget/Variance, Month, Year, etc.

Thanking you in advance. Any guidance will be helpful.

KPI			Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
I/P	Actual		4,163	4,002	3,871	14,872	5,118	5,738	5,554	5,434	6,334	6,243	5,941
I/P	Budget		4,058	4,058	3,912	4,156	3,912	3,912	4,205	4,010	4,253	4,156	4,253	3,999
I/P	Variance		105	(56)	(41)	10,716	1,206	1,826	1,349	1,424	2,081	2,087	1,688
O/P	Actual		557	608	575	712	673	553	589	559	792	788	705
O/P	Budget		435	416	416	435	376	406	396	396	445	416	416	433
O/P	Variance		122	192	159	277	297	147	193	163	347	372	289
Supplies	Actual		14,412	15,416	14,014	12,867	11,268	18,684	10,751	15,637	16,649	14,346	14,184
Supplies	Budget		12,472	12,423	12,050	12,722	11,948	12,025	12,747	12,249	12,995	12,673	12,921	12,316
Supplies	Variance		(1,940)	(2,993)	(1,964)	(145)	680	(6,659)	1,996	(3,387)	(3,654)	(1,672)	(1,263)
SWB	Actual		263,708	285,522	347,151	288,269	287,322	278,991	342,103	287,523	291,711	300,658	281,229
SWB	Budget		298,880	298,921	289,240	298,920	289,194	298,772	298,836	279,600	298,922	289,275	298,869	289,279
SWB	Variance		35,172	13,399	(57,910)	10,651	1,872	19,781	(43,267)	(7,923)	7,211	(11,383)	17,640

=If(
[ACTUAL / BUDGET] = 'Variance'

,

If(
(
If(((FLAG = 'Supplies' or FLAG = 'SWB') and [ACTUAL / BUDGET] = 'Variance'),
-1 * If(FLAG='I/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'I/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='O/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'O/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='SWB',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'SWB'}>}VALUE),
If(FLAG='Supplies',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'Supplies'}>}VALUE))))),
If(FLAG='I/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'I/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='O/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'O/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='SWB',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'SWB'}>}VALUE),
If(FLAG='Supplies',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'Supplies'}>}VALUE))))))
) > 0 , RGB(0,170,0),
If(
(
If(((FLAG = 'Supplies' or FLAG = 'SWB') and [ACTUAL / BUDGET] = 'Variance'),
-1 * If(FLAG='I/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'I/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='O/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'O/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='SWB',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'SWB'}>}VALUE),
If(FLAG='Supplies',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'Supplies'}>}VALUE))))),
If(FLAG='I/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'I/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='O/P',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'O/P'}, [RPT SECTION]={'*Stat*'}>}VALUE),
If(FLAG='SWB',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'SWB'}>}VALUE),
If(FLAG='Supplies',
Sum({$<Entity={"$(vEntitySelected)"}, DEPARTMENT={"$(vDepartmentSelected)"}, FLAG={'Supplies'}>}VALUE))))))
) < 0 , RGB(170,0,0),RGB(0,0,0)))
,
RGB(0,0,0))

[NickHoff · Accepted solution]

instead of using visual cues if you just want to color the text add logic to your text color on the dimension or expression you wish to change.  If your conditional logic is working correctly just add it there.

[vvira1316]

Hi Nick,

Yes, That works. Thanks for suggestion. One quick question on same. Is it possible to do Bold for variance using Text Format? If so, what is the syntax for it?

[vvira1316]

Nick,

I found the answer.

=If(
[ACTUAL / BUDGET] = 'Variance','<B>')

[NickHoff]

<'B'> is the syntax however, I'm not sure if it works in the table, but you can always do custom formatting on the text to make it bold.  This example shows where to do the custom format, if the option isn't available you'll have to enable it under your User Preferences > Design > Always Show Design Menu Items (checked)

[vvira1316]

Thanks again for sharing an alternative. The '<B>' formatting worked just fine. I've included screen shot of output.