Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
microwin88x
Contributor II

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 II

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
Community Browser