Dear All,
I need to calculate total remaining unit based on its aging.
Aging is total of days between Stock_In and Stock_Out for History model, and provided by user for Sold model.
I have a fact table that contain Stock History and Stock Sold, with details :
| TransTable | Model | Sold_Date | Stock_In | Stock_Out | Branch | Aging |
|---|
| History | A1 | | 01/09/2010 | 30/10/2010 | 1 | 59 |
| History | A1 | | 01/11/2010 | 30/12/2010 | 2 | 59 |
| History | A2 | | 01/11/2010 | 30/12/2010 | 2 | 59 |
| History | A3 | | 10/11/2010 | 30/12/2010 | 3 | 50 |
| Sold | A1 | 14/11/2010 | | | 2 | 14 |
Based on above sample, i use Sold Date as Date Dimension.
User requirment is confusing me, which is need to see remaining stock based on branch and aging.
So the result will be like sample below :
Year Selection : 2010
Month Selection : 11
| Branch | Aging | Remaining Stock |
|---|
| 2 | 59 | 1 |
| 3 | 50 | 1 |
Is there any solution for above issue with set analysis?
There's 1 requirement to count remaining stock based on selected sold month and year, with Branch as a dimension, and i able to show it with below set analysis.
( //count total model based on period of sold model
count(DISTINCT
{<TransTable={'History'}
,Year=, Month=
,Stock_In={'<=$(=max({<TableType={'Sold'}>}Sold_Date))'},Stock_Out={'>=$(=max({<TableType={'Sold'}>}Sold_Date))'}
>}
Model)
)
-
( //count total sold model
count(DISTINCT
{<TransTable={'Sold'}
,Year=, Month=
,Sold_Date={'<=$(=max({<TableType={'Sold'}>}Sold_Date))'}
>}
Model)
)
Many thanks,
Best Regards
