14 Replies Latest reply: Jul 21, 2016 8:00 AM by Jyoti Ranjan

# 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.

Thanks

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hey there Jyoti Nath,

I know what exactly you need. You need to create a Master Calendar. Doing so you get rid of problems when finding dates in between the ones you have. Take a careful look at this post in community:

Creating A Master Calendar

Hope this helps.

Regards,

MB

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hi Miguel,

Your solution helped me a lot.

Thanks.

You and Gysbert made my solution.

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Is this what you're trying to do: How to populate a sparsely populated field

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hi,

I have some dimensions.lets say

Account Type      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

Then i have to create another row.

Account Type        Credit                                                                                                              Date

P                       A.Credit+X.Credit  of previous date(05/06/2016)                                                      06/06/2016

P                       A.Credit+X.Credit  of previous date  (05/07/2016)                                                     06/07/2016

P                       A.Credit+X.Credit  of previous date  (06/07/2016)                                                      07/07/2016

P                      A.Credit+X.Credit  of previous date  (13/07/2016)                                                       14/07/2016

P                     A.Credit+X.Credit  of previous date     (14/07/2016)                                                     15/07/2016

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Account Type   Credit                                             Date

P             A.Credit+X.Credit  of previous date(05/06/2016)    06/06/2016

P             A.Credit+X.Credit  of previous date(05/07/2016)    06/07/2016

P             A.Credit+X.Credit  of previous date(06/07/2016)    07/07/2016

P             A.Credit+X.Credit  of previous date(13/07/2016)    14/07/2016

P             A.Credit+X.Credit  of previous date(14/07/2016)    15/07/2016

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

But there is no data for 05/06/2016. X.Credit of 05/06/2016 does not exist so P.Credit cannot be calculated.

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hi Gysbert,

I have mentioned the previous date of current date in that example.if current date is 6/06/2016 then its previous date is 5/06/2016

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

if current date is 6/06/2016 then its previous date is 5/06/2016

Really? Wow, imagine that! So the previous day of a date can be calculated by subtracting a day from a date? Amazing.

But that does not change that you have no data for 5/06/2016. So how is the credit amount for 5/06/2016 determined?

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

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

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Do you have some sample data with which we can try to help you better?

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hi,

I have some dimensions.lets say

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

AccountType    Credit                                                         Date

P             A.Credit+X.Credit  of previous date(05/06/2016)    06/06/2016

P             A.Credit+X.Credit  of previous date(05/07/2016)    06/07/2016

P             A.Credit+X.Credit  of previous date(06/07/2016)    07/07/2016

P             A.Credit+X.Credit  of previous date(13/07/2016)    14/07/2016

P             A.Credit+X.Credit  of previous date(14/07/2016)    15/07/2016

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

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

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

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)

'P'
as AccountType,
RangeSum(Credit, Lookup(Credit,LookUpValue,'X|'&Date(Date-1,'DD/MM/YYYY'),'T1')) as Credit,
Date
RESIDENT
T1
WHERE AccountType='A'
;

• ###### Re: How to Calculate Previous Date Value for a Dimension in Script

Hi Gysbert,