topic Sum If Function in QlikView

Sum If Function

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Sum If Function

sunny_talwar — Fri, 02 Jun 2017 16:25:28 GMT

May be like this in a straight table chart

Dimension

Customer

Expression

Sum(Quantity)

Sum(Amount)

Re: Sum If Function

Fri, 02 Jun 2017 17:23:25 GMT

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?

Re: Sum If Function

sunny_talwar — Fri, 02 Jun 2017 17:36:18 GMT

You don't want to see totals? disable totals

Re: Sum If Function

Fri, 02 Jun 2017 17:41:14 GMT

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...

Re: Sum If Function

sunny_talwar — Fri, 02 Jun 2017 17:55:55 GMT

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?

Re: Sum If Function

Fri, 02 Jun 2017 18:09:47 GMT

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.

Re: Sum If Function

sunny_talwar — Fri, 02 Jun 2017 18:25:05 GMT

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

Preparing examples for Upload - Reduction and Data Scrambling

Re: Sum If Function

johnw — Fri, 02 Jun 2017 21:48:02 GMT

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.