View all products without duplicates

[iglonator]

Hi all,

I have a sheet that has:

Product A +2

Product A -1

Product B +5

Product C +7

Product B -2

Now I would like to have a view that shows totals per product:

Product A 1

Product B 3

Product C 7

Do I need to make a sperate sheet or a slice? I tried different stuff, but I'm thinking in the wrong direction. I'm probably missing something pretty easy, but I can't seem to figure it out.  Thank you for your time and attention.

[Joseph_Seddik]

Yes you can make a slice per product and have the total with an expression like:

SUM(sliceName[quantity])

[iglonator]

Thank you for your reply Joseph, but I think making a slice per product is impossible when new products get added and deleted right?

[iglonator]

Ah! I could use the product sheet maybe? But I used that only to add new products so it can be later on subtracted and added in stock. Hmm I still think there is a better way

[Joseph_Seddik]

Deleting the product will not cause a problem; the slice will just be empty. 

Adding a product will however require manual creation of the corresponding slice, which, depending on the frequency of product additions, can be impractical. 

Other options:

• Use: SUM() and SELECT()
• A better, more complex solution, is to use Automation to update the Total per product, creating relevant actions and adapting your data structure accordingly. 

[iglonator]

Thank you Joseph_Seddik. I understand how to use SUM() and SELECT(), my problem is how to not show duplicates in the view.

[Joseph_Seddik]

You can use UNIQUE(), but beware of removing duplicates that you'd probably need when doing the SUM(). 

[iglonator]

Where do you use UNIQUE()? Another possibility is to use SELECT and "TRUE" to skip duplicates, but I do not know where to put it. A sliceconditions needs a Yes/No format. 

[Joseph_Seddik]

What duplicates you need to skip? duplicates based on what criteria? 

If you are doing a sum for quantities per product, surely you wouldn't want to skip lines with duplicate product ID. What do you need to skip duplicates for?

[Suvrutt_Gurjar]

Adding to @Joseph_Seddik 's guidance, if you are just looking a single "View" to aggregate those values you could try "Group By" setting in summary views. 

[Joseph_Seddik]

@Suvrutt_Gurjar Well done 🙂 probably that's what he was looking for from the beginning..

[iglonator · Accepted solution]

Thanks for all the help guys. I've found what I needed by trying. I've found the aggregate option earlier but this is not what I wanted.

I've added the sums behind every row by selecting and summing up. Although in the view all entries were in the view so lots of duplicates. I wanted to have one entry per product and behind it to total amount. I made it work with a slice and this row filter condition:

[id] = CONCATENATE(TOP(SELECT(sheet[id], [idproduct] = [_THISROW].[idproduct]), 1))

[Joseph_Seddik]

I don’t understand the purpose of this expression or how it would work as a slice row filter. 

[Suvrutt_Gurjar]

Good to know you have come up with a solution. You mentioned "Although in the view all entries were in the view so lots of duplicates."

You may wish to note that if you group by more than one column, you could show the grouping and aggregation in hierarchy or stages.