-
1. Re: Sum all rows with the same ID
msa s May 19, 2016 8:28 AM (in response to Carlos Edo){fixed seller id: sum(count)}
use fixed LOD
-
2. Re: Sum all rows with the same ID
Carlos Edo May 20, 2016 1:28 AM (in response to msa s)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
Carlos Edo Jun 6, 2016 2:42 AM (in response to Carlos Edo)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
John Sobczak Jun 6, 2016 3:10 AM (in response to Carlos Edo)1 of 1 people found this helpfulIf 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)}
-
5. Re: Sum all rows with the same ID
Carlos Edo Jun 6, 2016 4:42 AM (in response to John Sobczak)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?
Thanks in advance.
-
6. Re: Sum all rows with the same ID
John Sobczak Jun 6, 2016 5:01 AM (in response to Carlos Edo)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
Carlos Edo Jun 6, 2016 6:30 AM (in response to John Sobczak)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
John Sobczak Jun 6, 2016 6:55 AM (in response to Carlos Edo)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.