[ASK] Set Analysis count with date range

rendiyan — Wed, 23 Mar 2016 07:27:17 GMT

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

topic [ASK] Set Analysis count with date range in QlikView

[ASK] Set Analysis count with date range