Hi Vikas,
Currently you are calculating ageing buckets in the script, to make it dynamically calculate ageing bucket as on selected date or current date,
you will have to create one variable and then use it in the expression instead of field (InvDaysSelection)
So the steps would be:
1) Modify loading script as below
DIM_ProductBatchMaster:
LOAD
//BATCH_ID,
//DIV_ID,
//PARAM_VALUE_ID,
//UPPER(PROD_CODE) & '_' & UPPER(PROD_BATCH) AS PROD_CODE_BATCH_Key,
// UPPER(PROD_CODE) AS [Product Code],
UPPER(PROD_BATCH) AS [Product Batch Code],
Fact_Type AS [Fact Type Code],
Fact_Type_Desc AS [Fact Type],
//PRDFPLINK_GLCODE AS [Sales GL Code],
//SG_ID,
LocCode AS [Batch Location Code],
Loc_name AS [Batch Location],
Date(floor(MDATE),'DD/MM/YYYY') AS [Manufacturing Date],
Date(floor(EDATE),'DD/MM/YYYY') AS [Expiry Date],
Date(floor(EFFDATE),'DD/MM/YYYY') AS [Effective Date]
/*
,
If(num(Date(floor(EDATE))-Date(today()))>=1 and num(Date(floor(EDATE))-Date(today()))<=30,'1-30',
if(num(Date(floor(EDATE))-Date(today()))>=31 and num(Date(floor(EDATE))-Date(today()))<=60,'31-60',
if(num(Date(floor(EDATE))-Date(today()))>=61 and num(Date(floor(EDATE))-Date(today()))<=90,'61-90',
if(num(Date(floor(EDATE))-Date(today()))>=91 and num(Date(floor(EDATE))-Date(today()))<=180,'91-180',
if(num(Date(floor(EDATE))-Date(today()))>=181,'181+',
if(num(Date(floor(EDATE))-Date(today()))<1 ,'Expired',
if(IsNull(date(EDATE)) OR Date(EDATE)='','Date NA'))))))) AS InvDaysSelection
*/
//BACTIVE,
//BTHUPLDDATE,
//EXPINMONTH,
//BTCHUSER,
//BTHMRP,
//BTHAPPROVED,
//BTHAPPRBY,
//BTHAPPRDT
FROM
[Source QVDs SIM\DIM_ProductBatchMaster.qvd]
(qvd);
2) Create one variable and give name as (vDateForAgeing ) and assign below formulae to it.
=IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MAX(PostingDate)),DATE(Today()))
3) Then use it in ageing bucket expression like below
If(num(Date(floor([Expiry Date]))-vDateForAgeing)>=1 and num(Date(floor([Expiry Date]))-vDateForAgeing)<=30,'1-30',
if(num(Date(floor([Expiry Date]))-vDateForAgeing)>=31 and num(Date(floor([Expiry Date]))-vDateForAgeing)<=60,'31-60',
if(num(Date(floor([Expiry Date]))-vDateForAgeing)>=61 and num(Date(floor([Expiry Date]))-vDateForAgeing)<=90,'61-90',
if(num(Date(floor([Expiry Date]))-vDateForAgeing)>=91 and num(Date(floor([Expiry Date]))-vDateForAgeing)<=180,'91-180',
if(num(Date(floor([Expiry Date]))-vDateForAgeing)>=181,'181+',
if(num(Date(floor([Expiry Date]))-vDateForAgeing)<1 ,'Expired',
if(IsNull(date([Expiry Date])) OR Date([Expiry Date])='','Date NA')))))))
Please find attached a QVW file with an example for this query.
In the attached QVW :
if you have not selected any months or years in the list box, then ageing bucket will be calculated as on current date see below:
Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
