1 2 Previous Next 16 Replies Latest reply on Oct 12, 2016 10:54 AM by Peter Gabe

# Level of Detail Expression Alternative. Help needed.

Hi. I was hoping somebody could help me with the following situation:

I have two dimensions. [Iteration Number] and [Outcome] in a hierarchy and metric [Hit]. So various outcomes form an iteration number with a total hit count against it. So for example:

[Iteration Number]       [Outcome]         [Hit]

5                                       a                    3
b                    3

c                    3

6                                       d                    2

e                    1

I need to make a calculation that does the following. If an outcome contains 'b' or 'e' I want 'f' to be assigned against it: So the above would look like this:

[Iteration Number]       [Outcome]         [Hit]

5                                       a                    3
f                     3

c                    3

6                                       d                    2

f                     1

I  also need to make a calculation that does the following. If an outcome contains an entry, let's say 'f', then I want all of the outcomes for that iteration number to be 'f'. So the above would now look like this:

[Iteration Number]       [Outcome]         [Hit]

5                                      f                    3
f                    3

f                    3

6                                       f                    2

f                    1

Thanks.

• ###### 1. Re: Experiment Scenario. Combining and Assigning Outcomes

Hi Peter,

for the first one, you can create a calculated field

if contains([Outcome],"e") or contains([Outcome],"b")

then "f"

else [Outcome]

end

for second one, you can do sth like what's in the screenshot

hope this helps!

3 of 3 people found this helpful
• ###### 2. Re: Experiment Scenario. Combining and Assigning Outcomes

Hi Alex,

Thank you so much for your detailed answer. That certainly looks like it would work in any other case. However since I am using a data connection type (BQ) that does not support LoD expressions, this will unfortunately not be possible. Is there a way of achieving the same result without using the FIXED LoD expression?

Many thanks Alex.

• ###### 3. Re: Experiment Scenario. Combining and Assigning Outcomes

Hi good people,

I really need a solution to this without using a LoD expression. Any help would be appreciated!

• ###### 4. Re: Experiment Scenario. Combining and Assigning Outcomes

Hi, Peter

I don't follow all the conversation. but could you try below?

[Outcome 3]

if window_sum(sum(if [Outcome 2]="f" then ([Hit]) end))>0 then "f" else attr([Outcome]) end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 5. Re: Experiment Scenario. Combining and Assigning Outcomes

Thanks greatly Shin! You are a life saver!

Just very last thing, is it possible to display this as part of a stacked bar chart using [Lab] as columns, total [Hit] as column and [outcome 3] as the stacks? I've included an example of what I mean with more data and using the same solution you showed in the previous post. I've tried but it won't give me the desired result.

• ###### 6. Re: Experiment Scenario. Combining and Assigning Outcomes

Looks like yes.

Thanks,

Shin

• ###### 7. Re: Level of Detail Expression Alternative. Help needed.

Hi Shin,

That's almost there, I did get to that stage but I don't want [iteration number] to show. When I take it out or untick show header it doesn't work. Thanks for this Shin

• ###### 8. Re: Level of Detail Expression Alternative. Help needed.

I'm not sure what kind of viz you want?

Thanks,

Shin

• ###### 9. Re: Level of Detail Expression Alternative. Help needed.

Hi Shin. Sorry for not being clear. What I want is:

I did this by 'Compute using Lab' but it's wrong because total f is 4 because it is working out as per below:

Whereas the actual total should be 12. I did 'compute using lab' just to get this kind of viz up to show you because 'compute using outcome 2' doesn't work.

• ###### 10. Re: Level of Detail Expression Alternative. Help needed.

i think put the "iteration number" into "detail" and in Outcome 3 compute using "Outcome 2" will solve the color issue but still won't sum them up

• ###### 11. Re: Level of Detail Expression Alternative. Help needed.

Alex Xu you are correct. It partially solves the color issue (they are split) however it is not summed up:

• ###### 12. Re: Level of Detail Expression Alternative. Help needed.

Peter,

it's too much complicated thru table calc.

Played around but I could not figured it out. I' not good at table acalc and don't know it's technically achievable or not.

Thanks,

Shin

• ###### 13. Re: Level of Detail Expression Alternative. Help needed.

Hi Shin,

Yeah this one is complicated. Thanks so much for your help to get this far into the problem!

• ###### 14. Re: Level of Detail Expression Alternative. Help needed.

Hi all,

Summing up Hits into a single Outcome 3 Mark per Lab

is possible, but overly complicated -- up to the point

of being totally unusable in the case of real data.