Date Range Selection and Sum of av. Sales

[Anonymous]

hey

have a table which is shows inventory, sales and prodcuction issues by item and date.

Date (daily update) | ItemNo | InventoryOnHandQTY | ProductionIssueQTY | SalesIssueQTY

Now i would like to evaluate the a rolling average consumption by month (productionIssue + SalesIssues). The average should be calculated based on the past 12 months.

Additionally i like to go back to a specific date and see what the stock and average consumption was.

What i could find here in the forum is how to calculate the av. consumption

1. I made a variable vInventoryMaxDate

     =max([Date process])

2. I made a straight table dimension ItemNo and expression for the InventoryOnHand and one for the calculation of the av. consumption

     Sum ( {<Date ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Eveerything is working fine and the figures are correct, as long im not going to select any date. If i select a date, just that specific day will be calculated as consumption, even my date range is 365 days.

How can i make that the consumption expression will ignore the date selection and go back to pick all the figures?

If i set the identifier to 1, then the item selection will not work anylonger...

Sum (1{<Date ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Thansk in advance for your help

cheers Rob

[sunny_talwar · Accepted solution]

First of all you are the wrong placement of 1. You need it be between { and <

Sum ({1<Date ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}>} (#ProductionIssue + #SalesIssue)/12)

Second, are you making selection in Date field or another calendar field? If you are making in another field, you can add it to your set analysis so that it ignores any selection made in other calendar field:

Sum ({<Date ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}, Year, Month, Day, MonthYear, Quarter, QuarterYear>} (#ProductionIssue + #SalesIssue)/12)

[Anonymous]

Hi Sunny

Thansk first for support

Yes, sorry forgot about that, the data is linked with the Date field to the calendar table.

how should the second expression look like? Do i need to enter all fields from the calendar table, or just one value? Did try just to pick en unspecific value, see expresion below... It doesnt work

Sum ( {<[Date process] ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}, [Month process] ={'*'}>} (#ProductionIssue + #SalesIssue)/12)

many thanks

[sunny_talwar]

Wherever in the calendar table the user can make a selection in your application add that to your set analysis for the expression.

[Anonymous]

Ok, need just to add the modifier without any condition Thats easy... Now it works

Sum ( {<[Date process] ={">=$(=vInventoryMaxDate-365)<$(=vInventoryMaxDate)"}, %QuarterProcess, [Year process], [Month process],[Week process] >} (#ProductionIssue + #SalesIssue)/12)

i made like this

[Month process] ={'*'}

correct is like this

[Month process],

Cool, thanks for you help

[sunny_talwar]

Yup, not a problem