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

vvira1316 — Thu, 28 Jul 2016 15:04:36 GMT

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))

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

NickHoff — Thu, 28 Jul 2016 15:43:15 GMT

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.

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

vvira1316 — Thu, 28 Jul 2016 15:55:22 GMT

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?

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

vvira1316 — Thu, 28 Jul 2016 15:59:28 GMT

Nick,

I found the answer.

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

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

NickHoff — Thu, 28 Jul 2016 16:00:45 GMT

<'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)

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

vvira1316 — Thu, 28 Jul 2016 16:15:23 GMT

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

topic Re: Conditional Visual Cue in Pivot table based on only one value of a particular dim in QlikView

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

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

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

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

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

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