Im struggling with a qvw to model stocktake discrepancies in particular when trying to express value of loss as a percentage of turnover in the period.
What I have is a stockDiscrepancytable that contains for example:
Branch,ProductName,ProductCode,StockTakeID,DateFrom,DateTo,ShortageQTY,PriceAtStockTake
To calculate value of loss is easy ShortageQTY*PriceAtStockTake.
To calculate loss as a percentage of total Branch sales for period is where it gets tricky there 400 stores. I need to get the Total sales value for the stockTake Date periods for ALL Sales as well as for each product. ie we are short 200 of item "a" which is 1% of total sales for period and 30% of this products sales.
My sales table is a seperate fact table of transactions for each branch containing a Branch,Product,date,price,Qty
For some of my calcs i need to be joined down to only transactions that are in the discrepancy table and for others I need to be able to see all the sales Data. but i need to get at both at the same time. ie to create the following Pivot
Pivot: Branch,Item,Stocktake,DateFrom,DateTo DiscrepCount,DiscrepValue,PercentageSales,PercentageItemSales sorted in order of greatest loss
Ive tried several attempts but appear to be going round in Circles. What is the correct way to model this multiple fact Data ?
Colin R
