Multiple Dates confusion

[avastani]

I have 2 types of dates in my case, Sale Date (Weekly), Inventory Date(weekly), both for a Product and Store combination.

I would like to be able to analyze my data for both Sales and Inventory independently of each other's date selections, i.e. if I pick Sales for 2009, I should be able to pick Inventory for 2010 and so on. I tried with Set analysis to ignore my Calendar selections for Inventory and vice versa for Sales but it doesn't seem to work.

The expressions being

SUM({<InventoryYear=, InventoryWeek=>} QtySold) & SUM({<SalesYear=, SalesWeek=>} QtyInventory)

Am I missing something? I would have expected the engine to treat them both separately no matter what the other non-time dimensions are in the table or chart but it doesn't seem to do so. Any thoughts or suggestions are greatly appreciated. Thanks in advance

[avastani]

var SelectedLevel is the Level that the user selects on the front end. Available selections from the Crosslevels table.

[pover]

I think it would be easier to concatenae the SAP and Inventory tables instead of the link table, but regardless can you tell be which of the following formulas work? I have my suspicion that the real problem might be the aggr().

What is the result of the following formulas?

SUM( {<PriceLevel = {'$(varSelectedLevel)'}>}Price)

SUM( {<InventoryYear=, InventoryWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price)

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), SalesYear))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), ProductLine))

SUM( AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product))

SUM( {<InventoryYear=, InventoryWeek=>} AGGR(SUM(PriceLevel = {'$(varSelectedLevel)'}>}Price), Product, SalesYear, ProductLine))

Or play around with other variations to understand the behavior of the model. After seeing the model, I think this has more to do with the linktable then the handling of 2 dates.

Regards.

[avastani]

how would a concatenated table structure work because the calendar dates are not in common. so if one picks 2010 sales year, it would grey out inventory data unless i found a way to associate it together

[avastani]

Hi Karl,

I can see why you are saying the LinkTable is the culprit. I used

SUM( {<InventoryYear=, InventoryWeek=,PriceLevel = {'$(varSelectedLevel)'}>}Price) and the behavior was that I could select any Inventory Year or week and Prices didn't change. But when I surround that in an AGGR they fall apart. It wouldn't ignore the Inventory calendar selections.

The LinkTable is nothing but a full outer join of the Stores and SKU tables.