I have a concept that I do not know how to implement. I have a data source that shows sales history. The pertainent fields are order-date, invoice-date and quantity ordered.
What i want to do is show the open orders. If i have a date, say 5/30/2012, i would like to sum all the order quantities that have not invoiced by that date including orders placed before 5/30/2012 that have a invoice date after 5/30/2012.
Im running into a problem because I do not have an independent date range. When i try making a new calculation on orderdate to get [Day], I got all null values at first because the calculation was ultimately comparing the orderdate to itself.
I connected to an excel sheet but I am having diffuculty in linking the datasource to the excel sheet and work because I can't link the excel sheet with my data source. i made a calculation using the dates on the excel sheet that gives every day. However, I can't make the same calculation on the order date or invoice date because i get errors when calculation.
If someone can point in me in the right direction in making the appropriate calculation or joining the worksheets, i would very much appreciate it. Even if it is just describing the calculation needed in just words. Or if someone can show me how to make calculations between linked datasources because the following does not work... :
If attr([Order Date]) < attr([InventoryDatabase (Operations)].[Invoice_Date])
then (attr([InventoryDatabase (Operations)].[Qty_Ordered])) end