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

    Sum all rows with the same ID

    Carlos Edo

      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.

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

          {fixed seller id:  sum(count)}

           

          use fixed LOD

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

            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

              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
                John Sobczak

                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
                  Carlos Edo

                  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

                    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

                      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

                        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.