Expression Help - different facts

[avastani]

I am trying to create a table to obtain the number of weeks of Inventory data by Product Category and Sales Year given the datamodel here. The problem is that I have only partial weeks of data for 2010 and 2011 but I keep getting 53 as my number of weeks of inventory for my category which is incorrect.

My expression is simple

COUNT({SalesYear=, SalesMonth=, SalesQuarter=, SalesDate=} DISTINCT InventoryWeek)

What am I missing here? I asked the expression to ignore SalesYear but I am trying to pivot it on SalesYear and asking it to treat SalesYear as InventoryYear.. How can I do this? You can see if I pivot this on InventoryYear, I get the proper results.

[avastani]

Hi there all,

any takers on this question?

[rbecher]

Hi Amir,

what ist wrong with 53 weeks in 2009?? Some years have 53 weeks..

-- Ralf

[avastani]

yes but if I am in mid year, I have data for only partial weeks for Inventory

[rbecher]

You have no selection for SalesYear: {SalesYear=

[avastani]

right because I need it to ignore any selections for Sales Year but still be able to pivot it on that year

[rbecher]

But then the Count goes over all years. Remove this condition. I don't know why you have this set analysis...

[avastani]

do you think the data model would need to be revised then. because if I select a sales year, then my data for Inventory will not be independent of Sales Year anymore.

[rbecher]

I see, then I would suggest to have three expressions:

Weeks2009: COUNT(1<SalesYear= {2009} >} ...
Weeks2010: COUNT(1<SalesYear= {2010} >} ...
Weeks2011: COUNT(1<SalesYear= {2011} >} ...

[rbecher]

Another solution could be to decouple the tables...