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.
Thanks for your help,
Carlos.
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!
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!
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)}
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.
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
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?
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.
