Part 2 - Presenting chart bars as 100% value

[#1]

Hi, I have the same issue with another chart but this time it has 2 dimensions and an IF statement to calculate the result.

I'm struggling to create the same result as my previous question and present the information in a 100% value.

This is the current Expression

=if(Response_Level_2='Office',(c2_office_perc/1)*sum ({$<Source_Key={3}>}Ans_Percent)/(c2_office_split_perc_sum),sum ({$<Source_Key={2}>}Ans_Percent)/(c2_loc_split_sum_perc))

It take the information from Source Key 3 which is a breakdown of results for Office (calculating the results as a percentage for time spent at the "Desk","Open Space" and "Room") and also the data from Source Key 2 to provide the results for "Home" and "Out of Office".

The Chart it produces the following chart

Based on the previous question that was answered by whiteline - correct answer below for single dimension.

=Sum({$<Source_Key={1}>}  Ans_Percent)/Sum({$<Source_Key={1}>} TOTAL<Division_Name> Ans_Percent)

How would I make the expression above work with this suggestion.

Sorry about this but I have tried all sorts of options and configurations but I keep getting an error.

Any help you can provide again is much appreciated.

Thanks

Rob

[whiteline]

Hi again.

What are the statements marked bold in your expression ?:

=if(Response_Level_2='Office',(c2_office_perc/1)*sum ({$<Source_Key={3}>}Ans_Percent)/(c2_office_split_perc_sum),sum ({$<Source_Key={2}>}Ans_Percent)/(c2_loc_split_sum_perc))

And what dimension do you have now in addition to Devision_Name?

[#3]

Hi the statements are variables below

c2_office_perc

=sum ({$<Source_Key={2},Response_Level_2={'Office'}>}Ans_Percent)/c2_loc_split_sum_perc

c2_office_split_perc_sum

=sum ({$<Source_Key={3}>}Ans_Percent)

c2_loc_split_sum_perc

=sum ({$<Source_Key={2}>}Ans_Percent)

It might help if I add the qvw?

Thanks

Rob

[whiteline · Accepted solution]

Again, why do you use the variables in this way ?

Anyway, the whole your expression is:

=if(Response_Level_2='Office',

sum ({$<Source_Key={2},Response_Level_2={'Office'}>}Ans_Percent)

/sum ({$<Source_Key={2}>}Ans_Percent)

*sum ({$<Source_Key={3}>}Ans_Percent)

/sum ({$<Source_Key={3}>}Ans_Percent)

,

sum ({$<Source_Key={2}>}Ans_Percent)/sum ({$<Source_Key={2}>}Ans_Percent))

I think you could correct the ELSE case (bolded) by your self as it's almost equal to the discussed previously.

As for the first part of IF expresison, choose what sum is the right denominator and use total qualifier correspondingly.

[#5]

Well that was journey moment!!!!

Here is the final expression

if(Response_Level_2='Office',(sum ({$<Source_Key={2},

Response_Level_2={'Office'}>}TOTAL<Division_Name,Department_Name> Ans_Percent)

/sum ({$<Source_Key={2}>}TOTAL<Division_Name,Department_Name> Ans_Percent)/1)

*sum ({$<Source_Key={3}>}Ans_Percent)

/sum ({$<Source_Key={3}>}TOTAL<Division_Name,Department_Name> Ans_Percent),

if(Source_Key=2,sum ({$<Source_Key={2}>}Ans_Percent)/sum ({$<Source_Key={2}>} TOTAL<Division_Name,Department_Name> Ans_Percent),0))

Thanks for your direction - much appreciated.

The variables were to make it easier to work with and they are used in other charts, but it looks like they were getting in the way of things.

Thanks

Rob