Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to Calculate Previous Date Value for a Dimension in Script

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

14 Replies
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Gysbert_Wassenaar

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'
      ;


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Gysbert,

Thanks a lot.It is helpful.

I solved the issue.Your logic helped me a lot

Anonymous
Not applicable
Author

Hi Miguel,

Your solution helped me a lot.

Thanks.

You and Gysbert made my solution.