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

[pover]

It looks alright. Are you sure there is no other filter you are selecting that is modifying indirectly the result?

Regards.

[tabletuner]

it depends on how your data model looks like. Do you use concatenated or linktable datamodel to share the product and store dimensions for the fact tables? Are you able to post an example in a miniqvw in which the datamodel is displayed?

[avastani]

no filters selected. seems like it isn't working. i am going to try and create an outer join of the 2 calendar tables with the outer join or all product and store combinations which should work i am thinking.

[avastani]

i use a link table model.

[avastani]

i guess the double outer join solution won't be practical as it creates over a billion records. i probably need a minimal solution

[avastani]

It seems that where this falls apart is when I add the complexity of different Price Levels by Product.

My expression is

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

[pover]

Why don't you clear the dates in the Price formula?

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

or

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

[avastani]

then I get null as my Price. That's where I think the problem is.

It doesn't like {<InventoryYear=, InventoryWeek=>} in the SUM(Price) expression. Just don't understand why... any thoughts?

[pover]

Could you upload a picture of your data model highlighting the relationship of all the fields you use in the formula? What is the varSelectedLevel variable?

Regards.