Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator III

sum of Rows in a table

Hi!

I have a table

Column(1) = class(sales, 500)

Coulmn(2) = count (distinct Brand) / count (distinct total Brand)

Screenshot_61.jpg


The problem is that the column(2) does not sum up to 100%. I know the reason - some of my Brands have zero on negative Sales, or just don't have names (accounting software bug). I solved this by "Not showing zero values" in the Dimension in table properties, but I need to do something with Column(2) in order not to count those values as well.


The easiest way is to calculate Sum of Rows of the Column (1) and redo the formula:

Column(2) = Column (1) / [sum of rows of column (1)]


Is the way to achieve this? In fact i need a reference to a total (50 in my example).


Thank you

1 Solution

Accepted Solutions
sunny_talwar

What if you do this

Count(DISTINCT [Brands])/Count(TOTAL DISTINCT Aggr(If(Sum(InventoryBalance)/Sum(Sales) > 0, [Brands]), Brands))

View solution in original post

11 Replies
sunny_talwar

What is your expression for Column(1)?

ziabobaz
Creator III
Creator III
Author

i will try to simplify it, because the original formula is a monster with set analysis:

class(

aggr(

if(

Sum(Sales)

=0,

'10000',

Sum(InventoryBalance)/Sum(Sales))

, [Brands]),   

    500)

sunny_talwar

I was talking about the expression for this guy here

Capture.PNG

ziabobaz
Creator III
Creator III
Author

Sorry

count( DISTINCT [Brands])

sunny_talwar

So this isn't working?

Count(DISTINCT [Brands])/Count(TOTAL DISTINCT [Brands])

ziabobaz
Creator III
Creator III
Author

it shows more values than I need, because it includes Brands that have sum(InventoryBalance) / sum(Sales) <=0.

I excluded those values in the table properties (unchecked  "show zero values''), so the GUY row shows me 50 and the next column shows me 54.

sunny_talwar

What if you do this

Count(DISTINCT [Brands])/Count(TOTAL DISTINCT Aggr(If(Sum(InventoryBalance)/Sum(Sales) > 0, [Brands]), Brands))

Anonymous
Not applicable

Did you check for the round off?The totals mismatch sometimes when they are rounded off..

ziabobaz
Creator III
Creator III
Author

Yeeeeeeeees!