Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
how to calculate Previous Date value for a Dimension in Script.
I have already used Previous().But here i am getting previous row data.but i need Previous date data.bcz in some cases Exact previous date is not available.
If i have 14th Jul,15th Jul ,16th Jul and 19th Jul data then i m getting problem for creating 19th Jul Dimension Data.bcz there is no 18th Jul data.it extracts 16th Jul data(Previous row).
I have to add an row with a dimension name and its value with previous date value.
Ex:
Current Credit Of Add to reimbursement=X+Y+Z+Previous date Credit of Add to reimbursement
X,Y,Z are other previous date values for other dimensions.
Please help me to find out the solution.
Thanks
if there is no data then i have to show zero.
if previous date has data then i will pick that values for the current date and dimension P
My table:
AccountType Credit Date
A 200 06/06/2016
A 200 06/07/2016
A 200 07/07/2016
A 4000 14/07/2016
A 5000 15/07/2016
X 1000 06/06/2016
X 1000 06/07/2016
X 1000 07/07/2016
X 4000 14/07/2016
X 5000 15/07/2016
Required rows to insert in My Table
AccountType Credit Date
P A.Credit+X.Credit+P.Credit of previous date(05/06/2016) 06/06/2016
P A.Credit+X.Credit+P.Credit of previous date(05/07/2016) 06/07/2016
P A.Credit+X.Credit+P.Credit of previous date(06/07/2016) 07/07/2016
P A.Credit+X.Credit+P.Credit of previous date(13/07/2016) 14/07/2016
P A.Credit+X.Credit+P.Credit of previous date(14/07/2016) 15/07/2016
Try this:
T1:
LOAD *, AccountType&'|'&Date as LookUpValue INLINE [
AccountType, Credit, Date
A, 200, 06/06/2016
A, 200, 06/07/2016
A, 200, 07/07/2016
A, 4000, 14/07/2016
A, 5000, 15/07/2016
X, 1000, 06/06/2016
X, 1000, 06/07/2016
X, 1000, 07/07/2016
X, 4000, 14/07/2016
X, 5000, 15/07/2016
];
CONCATENATE(T1)
LOAD
'P' as AccountType,
RangeSum(Credit, Lookup(Credit,LookUpValue,'X|'&Date(Date-1,'DD/MM/YYYY'),'T1')) as Credit,
Date
RESIDENT
T1
WHERE AccountType='A'
;
Hi Gysbert,
Thanks a lot.It is helpful.
I solved the issue.Your logic helped me a lot
Hi Miguel,
Your solution helped me a lot.
Thanks.
You and Gysbert made my solution.