Set analysis division

[#1]

Hi,

I've created a formula using set analysis in my chart:

=Sum({$<Source={'LBE'}>}Sales) / Sum({$<Source={'AOP-Ex Factory'}>}Sales)

I'm essentially trying to divide sales total under LBE by sales total under 'AOP-Ex Factory' to get a percentage.

Individually the numerator and denominator work and produce line graphs. However, doing one divided by the other doesn't work. Where am I going wrong?

Thanks in advance!!

[tresesco]

If Source is a dimension in your chart, try like:

=Sum(Total {$<Source={'LBE'}>}Sales) / Sum(Total {$<Source={'AOP-Ex Factory'}>}Sales)

[Anonymous]

Assuming it is a true chart and not a table, then how about creating the 2 expressions numerator and denominator that you say work and setting them on the expression tab to Invisible.

Then create a 3rd expression:

    = [numerator} / [denominator]

[#4]

Hi both,

They both seem to work but I also have 'Month' as a dimension and this formula gives me the total numerator divided by the total numerator for the whole year whereas I want this split by month on a chart. I assumed this formula when shown on a chart with month as dimension would do this automatically but it doesn't...?

Thanks

[#5]

Hi Kevin,

your expression Sum({$<Source={'LBE'}>}Sales) / Sum({$<Source={'AOP-Ex Factory'}>}Sales) should work, can you try taking out the month dimension from the chart and viewing the result?

It sounds like it is the dimension causing you the issue, can you check your underlying data, does source 'LBE' have sales data in the same month as source 'AOP-Ex Factory'? If not your expression will be trying to break the result down on the month and potentially dividing by zero and not displaying due to that

hope that helps

Joe

[#6]

Hi Joe,

I've tried removing the month but that doesn't work either. I have checked the raw data too and there is data for LBE and AOP-Ex Factory in all months.

Very odd!

[#7]

Can you knock up an example if possible? Would be easier to help resolve I think.

Can you also try

Sum({1<Source={'LBE'}>}Sales) / Sum({1<Source={'AOP-Ex Factory'}>}Sales)

Just to see if there is some underlying selection you have which is affecting the output

[#8]

I've tried to replicate this in the attached example...

There are two graphs, the first is where I'm attempting to combine a bar graph showing LBE and AOP and add a line graph showing SvT.

The second graph is where I've just tried to do the SvT line.

There is a table showing the data.

Many thanks!

[#9 · Accepted solution]

Ah I see, you have source as a dimension too, so that division will never work as the results will be split across the source dimension.

Remove source and you get the result

See attached, you'll need to create a second sales expression and remove the source dimension

[#10]

Brilliant! Many thanks, works like a charm!