Multiple Condition Set Analysis

[#1]

Hi All - I have a set analysis question that will probably sound more complicated than it is. I'm trying to create both a bar chart/pivot table that will show the amount of product traded, relative to the overall volume traded during a certain time period, but I'm having issues with my set analysis statement.

In this world, a company partakes in an event where they can acquire more of a specific product at a discount (what I'll refer to as a "Product Event"). Other companies may also participate, and companies acquire many different types of products at various Product Events, not just one product in particular. Each company's inventory, or the amount of each product they own, is known, but only at specific dates in time. In the example attached, these dates are Nov 1, 2016, and Dec 1, 2016. What I want to do is - after a company partakes in one of these "Product Events" - figure out the amount that they sold relative to how much is Traded in the market (the volume).

For example, per the attached, for Product Event Z that occurred on Nov 25, 2016, I want to back out how much Company AAA sold of the amount they acquired on Nov 25, relative to the volume in the market in that time frame. I am only given a daily volume avg, so I need to multiply this by the amount of days between the Product Event and the Period End date (which is when the company will next report its inventory). So in this case, for Company AAA, I would have:

[100,000(Starting inventory) + 10,000 (Amount acquired in Product Event) - 105,000 (Ending inventory) ] / [10,000* (4 Business days from Nov 25, 2016 to Dec 1, 2016) = 12.5%. So 12.5% would be the amount shown on my bar graph for Company AAA, in addition to the other bars for other Companies for a given Product Event. What adds a layer of complication here is, I don't want to evaluate this expression when the Starting Balance + Amt acquired in Product Event is greater than the Ending Balance (since I would not be able to definitively back out how much was sold from the Product Event). So Company CCC would result in something like "-" or some way of distinguishing the expression can't be evaluated.

Is there a set analysis expression for the above?

[sunny_talwar]

I am not sure why wouldn't company CCC will be excluded here. If I go by your logic, I would essentially exclude all the rows here

I don't want to evaluate this expression when the Starting Balance + Amt acquired in Product Event is greater than the Ending Balance. So Company CCC would result in something like "-" or some way of distinguishing the expression can't be evaluated.

If I look at all possible rows, I see that the Starting Balance + Amt Acquired is greater than the Ending Balance

So, is your condition correct or am I missing something important here?

To do the calculations, assuming this is correct

[100,000(Starting inventory) + 10,000 (Amount acquired in Product Event) - 105,000 (Ending inventory) ] / [10,000* (4 Business days from Nov 25, 2016 to Dec 1, 2016) = 12.5%.

I used this

=If(ProductValueDate1+ProductValueDate2 > ProductValueDate3, RangeSum(ProductValueDate1, ProductValueDate2, -ProductValueDate3)/(DailyMktAvgProductTraded*(NetWorkDays([Date2-ProductEvent], [Date3-PeriodEnd])-1)))

Dimensions

Company

ProductID

The above may not be what you are looking to get, but I was not completely sure what you wanted. May be clarify the expected output a little bit and we might be able to help you better.

Best,

Sunny

[#3]

Thanks Sunny. Sorry - that should have read that I want to exclude where the Starting Balance (A) + Amt acquired in Product Event (B) - the Ending Balance (C) is greater than the Amt acquire in Product Event (B).

So A+B-C > B --> A-C>0. So CCC would thus be excluded. Thanks for your help!

[sunny_talwar]

May be this:

=If(ProductValueDate1-ProductValueDate3 <= 0, RangeSum(ProductValueDate1, ProductValueDate2, -ProductValueDate3)/(DailyMktAvgProductTraded*(NetWorkDays([Date2-ProductEvent], [Date3-PeriodEnd])-1)))

[#5]

Yes thanks. What would it be if the ProductValues were stored in one column and the date was in another?

[sunny_talwar]

Would you be able to elaborate on what exactly are you changing here?

[sunny_talwar]

Also, when you get a chance, can you follow me here so that I can send you a private message or you can send me a private message.

Best,

Sunny