Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.