Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
microwin88x
Contributor III

SUM with Division

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 
1ACTUALMET110
1BUDGETMET130
1ACTUALMET215
1BUDGETMET255
2ACTUALMET15
2BUDGETMET133
2ACTUALMET245
2BUDGETMET285

 

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

5 Replies
vishsaggi
Esteemed Contributor III

Re: SUM with Division

Can you share some sample data with an expected output ?
microwin88x
Contributor III

Re: SUM with Division

Here's an example:

ID TYPE METRIC VALUE 
1ACTUALMET110
1BUDGETMET130
1ACTUALMET215
1BUDGETMET255
2ACTUALMET15
2BUDGETMET133
2ACTUALMET245
2BUDGETMET285

 

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

vishsaggi
Esteemed Contributor III

Re: SUM with Division

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

Re: SUM with Division

Try this

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

Re: SUM with Division

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein