Sum If Function

[#1]

Hi Everyone,

I have a data set filtered to a specific set of dates (one week).

I'm just trying add quantity and amount if the Customer names are the same, but I'm not sure how.  Would you be able to help:  For example, I want to reduce the following so that there are only 2 lines (one for customer AA and one for customer BB):

Customer	Quantity	Amount
AA	1	100
AA	-1	-100
AA	2	25
BB	1	1300
BB	0.5	500

Thank you for your help!

[sunny_talwar]

May be like this in a straight table chart

Dimension

Customer

Expression

Sum(Quantity)

Sum(Amount)

[#3]

Thanks for the quick response!  When I do that, it is also summing a total line from somewhere.  That's why I was hoping to keep the Quantity and Amount as dimensions rather than expressions.  Any advice?

[sunny_talwar]

You don't want to see totals? disable totals

[#5]

It's more like when I run a regular Sales by Customer Detail Report it looks like:

                         Quantity

Company AA           1

Total:                       1

So when I try to build a separate advanced report and just pull in Transactions.Quantity, I get 1.  But when I do sum(Transactions.Quantity), I get 2.  I'm assuming it's adding in the total from the Sales by Customer Detail Report; but that's a canned report and completely different, right?  I'm a little confused why summing it adds it up...

[sunny_talwar]

Looking at that, I still don't understand what you have. Would you be able to share a sample or share some sample mocked up data with your expectation of what you want to see as an output from the sample data or sample app?

[#7]

Sure, thanks for your help:

When Transactions.Quantity and Transactions.Amount are Dimensions, I get the following (which is correct):

                              Quantity          Amount

Company AA               1                    75

Company AA               -1                   -75

Company AA               1                    100

But when I make Transactions.Quantity and Transactions.Amount both Expressions and sum them like this sum(Transactions.Quantity) and sum(Transactions.Amount), I get the following:

                         Quantity          Amount

Company AA          2                    200

I can't seem to figure it out and in the expressions section, I did not click the Show Totals option.

[sunny_talwar]

Oh this seems very weird to me... I would have expected 1 and 100....

Preparing examples for Upload - Reduction and Data Scrambling

[johnw]

You might have duplicate rows in your data model. When you're showing dimensions, it shows distinct combinations of those dimensions. You could have 1, 2, or 99999999 copies of the row, and the first chart would still look the same. The second chart, though, would show a difference. It's very easy to accidentally duplicate rows when doing a bunch of joins, for instance.