2 Replies Latest reply: Aug 18, 2009 11:59 AM by Matt Sweeney

# Set Analysis - Sales Order Quantity Prior to Date Selections

Hi all,

In a Pivot Table I wish to provide:

- Sales Order Quantity PRIOR to the Current Date Selections to provide an Opening Balance. The Date Selections are list boxes Month and Year.

Sales Order Quantity within the Current Date Selections is easy of course, but i'm not sure how to sum the Sales Order Quantity for all Dates (Years and Months) Prior to the current selection.

Any ideas?

Thanks!

• ###### Set Analysis - Sales Order Quantity Prior to Date Selections

Pseudocode:

Sum of SalesOrderLineOrderedQuantity where the Year is less than the Currently selected year

Formula(incorrect)

sum({\$<Year= {<\$(Year)}>}SalesOrderLineOrderedQuantity)

Pseudocode

Sum of SalesOrderLineOrderedQuantity where Month is less than the Currently selected Month.

I want to combine these two into the one formula.

• ###### Set Analysis - Sales Order Quantity Prior to Date Selections

This was the solution:

Sum({\$<Year=, Month=>} If(Date < vMaxDate,SalesOrderLineOrderedQuantity))

it's getting hard to get a response on here with the number of posts created increasingly dramatically!