topic SUM with Division in QlikView

SUM with Division

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following Expression:

SUM([Sales A] / [Sales B]) // working - when I clear all filters (for example Customers) I see the total value

SUM([Sales A]) / SUM([Sales B]) // not working - when I clear all, I see a number that's not the total value

The thing is I made an optimization to the Data Model and now I would have to use something like:

SUM({<METRIC={'ACTUAL'}>}Sales) / SUM({<TYPE={'B'}>}Sales) - but it's not working because I don't get the total (it just works when I filter a Customer for example)

Is there any way to make the division in the same function? Or any way instead to get the same result?

Thank you!!!

Here's a demo:

ID	TYPE	METRIC	VALUE
1	ACTUAL	MET1	10
1	BUDGET	MET1	30
1	ACTUAL	MET2	15
1	BUDGET	MET2	55
2	ACTUAL	MET1	5
2	BUDGET	MET1	33
2	ACTUAL	MET2	45
2	BUDGET	MET2	85

My expression where I need to divide value with metric1 over metric2 (works ONLY when I select an ID):

=SUM({<TYPE={'ACTUAL'},METRIC={'MET1'}>}VALUE)
/ SUM({<TYPE={'ACTUAL'},METRIC={'MET2'}>}VALUE)

for ID = 1 I get 0.66

for ID = 2 I get 0.11

but when nothing selected it shows 0.25 and it should show 0.66+0.11 = 0.77

Re: SUM with Division

vishsaggi — Thu, 10 Jan 2019 03:25:40 GMT

Can you share some sample data with an expected output ?

Re: SUM with Division

microwin88x — Thu, 10 Jan 2019 03:43:49 GMT

Here's an example:

ID	TYPE	METRIC	VALUE
1	ACTUAL	MET1	10
1	BUDGET	MET1	30
1	ACTUAL	MET2	15
1	BUDGET	MET2	55
2	ACTUAL	MET1	5
2	BUDGET	MET1	33
2	ACTUAL	MET2	45
2	BUDGET	MET2	85

My expression where I need to divide value with metric1 over metric2 (works ONLY when I select an ID):

=SUM({<TYPE={'ACTUAL'},METRIC={'MET1'}>}VALUE)
/ SUM({<TYPE={'ACTUAL'},METRIC={'MET2'}>}VALUE)

for ID = 1 I get 0.66

for ID = 2 I get 0.11

but when nothing selected it shows 0.25 and it should show 0.66+0.11 = 0.77

Re: SUM with Division

vishsaggi — Thu, 10 Jan 2019 04:01:41 GMT

May be try using TOTAL qualifier like Sum(Total ...)/Sum(Total ....)
After total write your set expr

Re: SUM with Division

sunny_talwar — Thu, 10 Jan 2019 12:42:06 GMT

Try this

Sum(Aggr(
Sum({<TYPE = {'ACTUAL'}, METRIC = {'MET1'}>} VALUE)/
Sum({<TYPE = {'ACTUAL'}, METRIC = {'MET2'}>} VALUE)
, ID))

Re: SUM with Division

jonathandienst — Thu, 10 Jan 2019 13:10:17 GMT

These two expressions are NOT the same arithmetically:

SUM([Sales A] / [Sales B]) -- sum of ratios (sum of rows total)
SUM([Sales A]) / SUM([Sales B]) -- ratio of sums (expression total)

That's why the result differ. If you want the sum of rows total in a pivot table, the Aggr() logic posted by Sunny should work.