set analysis

sspawar88 — Tue, 14 Jul 2015 12:22:15 GMT

i have

one bar chart for region(stack style)---dimension=region_name

one bar chart for branch_type---dimension=branch_type

and one pivot table.

in both bar chart i have taken 2nd Calculated dimension are as follow:

if(param_name='Temperature' and (param_value)<='-1' and (param_value)>= '-10', 'Red',

if(param_name='Temperature' and (param_value)<= '-10.1' and (param_value)>='-13.9', 'Yellow',

if(param_name='Temperature' and (param_value)<= '-14' and (param_value)>='-17.9', 'Amber',

if(param_name='Temperature' and (param_value)<= '-20', 'Green'))

))

in which i ignored the range between -18 to -19.9

--------------------------------------------------------------------------------------------------------------------------------------------------

In Bar Chart for region

i used following expression

Count({$<param_name={'Temperature'},param_value={'<=-1'}>}param_value)/

Count(total <region_name>{<param_name={'Temperature'},param_value={'<=-1'}>}param_value)

so that it shows 100% for each bar in which i shown the percentage in stack for each range as per above Calculated dimension.

for color of stack i used background color expression are as follow:

if(Count({<param_name={'Temperature'}, param_value={'<=-1>=-10'}>} param_value)

/Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <region_name> param_value),rgb(255,0,0),

if(Count({<param_name={'Temperature'}, param_value={'<=-10.1>=-13.9'}>} param_value)

/Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <region_name> param_value),rgb(255,255,51),

if(Count({<param_name={'Temperature'}, param_value={'<=-14>=-17.9'}>} param_value)

/Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <region_name> param_value),RGB(255,178,102),

if(Count({<param_name={'Temperature'}, param_value={'<=-20'}>} param_value)

/Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <region_name> param_value),RGB(102,204,0)))))

---------------------------------------------------------------------------------------------------------------------------------------------------

same in In Bar Chart for branch_type i used following expression

Count({$<param_name={'Temperature'},param_value={'<=-1'}>}param_value)/

Count(total <branch_type>{<param_name={'Temperature'},param_value={'<=-1'}>}param_value)

so that it shows 100% for each bar in which i shown the percentage in stack for each range as per above Calculated dimension.

i have taken background color expression diffrentlly as per branch_type.

---------------------------------------------------------------------------------------------------------------------------------------------------.

In pivot table i have taken region_name and branch_type dimensions

i have taken four expression for four ranges for example--expression for % compliance for range of param_value '<=-20' which not match with the stack in above both bar charts.

i used following expression for this

Count({$<param_name={'Temperature'},param_value={'<=-20'}>}param_value)/

Count({1<param_name={'Temperature'},param_value={'<=-1'}>}param_value)*100

----------------------------------------------------------------------------------------------------------------------------------------------------

when i click on west in region bar chart and see the value in branch bar chart for diffrent stack, it is different from my pivot.

i think pivot values are correct.

what i need to change in expression to get the same value in pivot and branch bar chart and region bar chart.?

i need total 100% for sum of all ranges percentage in pivot and charts.

please suggest.

thanks in advance.

topic set analysis in QlikView

set analysis