topic Re: Set analysis division in QlikView

Set analysis division

Tue, 30 Dec 2014 12:59:22 GMT

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!!

Re: Set analysis division

tresB — Tue, 30 Dec 2014 13:03:56 GMT

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

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

Re: Set analysis division

Anonymous — Tue, 30 Dec 2014 13:07:17 GMT

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]

Re: Set analysis division

Tue, 30 Dec 2014 13:32:32 GMT

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

Re: Set analysis division

Tue, 30 Dec 2014 13:41:26 GMT

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

Re: Set analysis division

Tue, 30 Dec 2014 14:32:51 GMT

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!

Re: Set analysis division

Tue, 30 Dec 2014 14:46:47 GMT

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

Re: Set analysis division

Tue, 30 Dec 2014 15:15:10 GMT

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!

Re: Set analysis division

Tue, 30 Dec 2014 15:27:22 GMT

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

Re: Set analysis division

Tue, 30 Dec 2014 15:48:24 GMT

Brilliant! Many thanks, works like a charm!

Re: Set analysis division

Tue, 30 Dec 2014 16:07:04 GMT

no problem, glad to help.