7 Replies Latest reply on Apr 4, 2017 12:19 PM by Guy Adams

I'm sure this is a very simple solution, but I just cannot figure it out.

I want to sum the total Increment ID Product Sales, if the Increment ID includes Product A or Product B in the Name (Catalog Product Flat 1).

Example:

 Product Sales Increment Id Name (Catalog Product Flat 1) \$12.31 100008B1S Product C \$4.70 100008B1S Product C \$2.33 100008B1S Product A \$19.34

I'm using 10.1.5

Thanks,

Guy

Hope it Helps!!!

Let me know

Thanks

Deepak

1 of 1 people found this helpful

Here are exact figures:

hi Guy,

So I think I've understood the requirements...let me know if not (certainly wouldn't be the first time!!)

So one way to do this is using sets. Sets are a bit like FIXED LoDs, in that we can assess some condition (has to be a boolean, T/F) in aggregate and return the result at row level, as the specified level. So in this case we want to flag an increment ID if it has bought Product A or B...and then return this flag to the increment ID so we can capture all their sales.

So first I create a set off [Increment ID]...using the below condition

MAX(IIF([Name (Catalog Product Flat 1)] = 'Product A'

OR [Name (Catalog Product Flat 1)] = 'Product B',1,0))=1

Once we have this, we can return just the sales for the increment IDs that are in this set (and for all their sales, not just product A and/or B)

[Product Sales for Increment ID buying Prod A or B]

IIF([Increment IDs containing Prod A or B],[Product Sales],NULL)

Hope that helps, and makes sense.

1 of 1 people found this helpful

Thank you for the quick response, Deepak.

I can't see the attachment, can you tell me what the formula you used for the ATTR(A or B) calculation?

Thanks again.

Guy

Simon,

This worked perfectly. Thank you!

Guy

The attachments are there. If you go to main thread, you would find everything, but not from the email. Have a look at screenshot.

Thanks

Deepak