topic Re: How to Calculate Previous Date Value for a Dimension in Script in App Development

How to Calculate Previous Date Value for a Dimension in Script

Anonymous — Tue, 19 Jul 2016 08:51:36 GMT

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

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

miguelbraga — Tue, 19 Jul 2016 09:30:02 GMT

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

This guide will help you to deal with your questions

Hope this helps.

Regards,

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

Gysbert_Wassenaar — Tue, 19 Jul 2016 09:41:50 GMT

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

sunny_talwar — Tue, 19 Jul 2016 10:01:22 GMT

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

Anonymous — Tue, 19 Jul 2016 10:25:22 GMT

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

Anonymous — Tue, 19 Jul 2016 10:28:52 GMT

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

Gysbert_Wassenaar — Tue, 19 Jul 2016 10:29:49 GMT

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

Anonymous — Tue, 19 Jul 2016 10:31:59 GMT

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

Anonymous — Tue, 19 Jul 2016 10:33:55 GMT

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

Gysbert_Wassenaar — Tue, 19 Jul 2016 10:47:40 GMT

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

Anonymous — Tue, 19 Jul 2016 10:51:15 GMT

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

Anonymous — Tue, 19 Jul 2016 11:55:17 GMT

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

Gysbert_Wassenaar — Tue, 19 Jul 2016 12:33:10 GMT

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

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

Anonymous — Thu, 21 Jul 2016 12:00:56 GMT

Hi Gysbert,

Thanks a lot.It is helpful.

I solved the issue.Your logic helped me a lot

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

Anonymous — Thu, 21 Jul 2016 12:04:20 GMT

Hi Miguel,

Your solution helped me a lot.

Thanks.

You and Gysbert made my solution.