8 Replies Latest reply on Jun 6, 2016 6:55 AM by John Sobczak

# Sum all rows with the same ID

Hi everyone,

What I'm trying to do I think it's very easy, but I couldn't find a way to do it.

I've been searching on the forums and tried some solutions but I couldn't get it to work.

My data is something like the following:

I have many records for each Seller id, each of them with its date. The Count field can be 1 or -1.

I want to calculate the Count SUM of all the rows with the same Seller id.

Carlos.

• ###### 1. Re: Sum all rows with the same ID

{fixed seller id:  sum(count)}

use fixed LOD

• ###### 2. Re: Sum all rows with the same ID

But this would sum all of them without takin into account the Date, right?

I didn't explain in correctly in my first post.

Each record is the start or end of sales of a product owned by the seller. If Count is 1, it's the start, and if Count is -1, then it's the end.

So, by doing a SUM() of Count I'm able to know how many Sellers had active products at a specific date.

How could I do this?

Thanks!

• ###### 3. Re: Sum all rows with the same ID

msa s

Still cannot fix it with `fixed`.

Does it sum all of the Count fields without taking into account the date filters? Because the result I see is always 0.

Is there any other solution?

Thank you!

• ###### 4. Re: Sum all rows with the same ID

If you add your date filter to context or if you add date to the fixed expression it should work.  But if your date has a timestamp to it you will want to truncate it to day.

{fixed [seller id], [date]:  sum(count)}

1 of 1 people found this helpful
• ###### 5. Re: Sum all rows with the same ID

Ok, that seems to work better. Thank you!

But now I'm facing a different problem,

I create a field like this:

Active Events per Seller = {fixed [seller id], [date]:  sum(count)}

And then, to calculate the Active Sellers, I do:

Active Sellers = IF [Active Events per Seller] > 0 THEN [Seller] END

The problem here is that, when displayed with the MONTH dimension in the Columns it only takes into account the data for that month, instead of from the very beginning.

I would like to do something like the following, but it's not allowed:

Active Sellers = IF RUNNING_SUM([Active Events per Seller]) > 0 THEN [Seller] END

Is there any solution?

• ###### 6. Re: Sum all rows with the same ID

Does the below work?  You need to have all aggregates in any expression with a table calc so I made the Seller dimension an aggregate.

Active Sellers = IF RUNNING_SUM([Active Events per Seller]) > 0 THEN max([Seller]) END

• ###### 7. Re: Sum all rows with the same ID

No, the result is really weird. And much higher than expected.

With MIN() the result is also wrong.

Thanks anyway.

Can you think of any other solution?

• ###### 8. Re: Sum all rows with the same ID

It's hard to say without know what the composition of the view/sheet looks like.  Usually if you already have the seller dimension as a level of detail in the view it should work.  One way to force it to level of detail is add the seller dimension to the detail marks card, but not knowing what your sheet/viz looks like I don't know if that will cause adverse effects of the viz.