LTD Factor

[rohit214]

Hi all

I have a situation like in  table there are Date Fund_ID, Value column

i want to subtract each date value with the previous  monthend value shown in excel file

my requirement is shown in the attached file

please find the attachment

thanks

rohit

[rohit214]

any one....

[jonathandienst]

Hi

This will do it in the load script:

Data:
LOAD *,
          If(Floor(MonthEnd(Date)) = Date, Value, Peek(PreviousMonthEnd)) As PreviousMonthEnd
;
LOAD Date(Date#(Date, 'DD-MM-YYYY')) As Date,
          Value
Inline
[
          Date           Value 
          30-11-2012           -610 689.11 
          03-12-2012           -678 582.87 
          04-12-2012           -657 858.50 
          05-12-2012           -554 619.26 
          06-12-2012           -361 089.32 
          07-12-2012           -394 353.08 
          10-12-2012           -317 774.98 
          11-12-2012           -295 481.55 
          12-12-2012           -257 925.10 
          13-12-2012           -352 176.60 
          14-12-2012           -409 172.65 
          17-12-2012           -490 254.56 
          18-12-2012           -557 208.12 
          19-12-2012           -495 645.69 
          20-12-2012           -613 676.28 
          21-12-2012           -701 969.60 
          24-12-2012           -700 221.15 
          27-12-2012           -598 306.64 
          28-12-2012           -682 375.43 
          31-12-2012           -489 755.70 
          02-01-2013           382 343.40 
          03-01-2013           302 880.62 
          04-01-2013           85 793.47 
          07-01-2013           88 147.47 
          08-01-2013           76 306.57 
          09-01-2013           289 780.85 
          10-01-2013           398 835.81 
          11-01-2013           247 108.91 
] (delimiter is '\t')
;

Hope that helps

Jonathan

[rohit214]

Hi,

thanks for reply unfourtunatly its not working

thanks

rohit

[Gysbert_Wassenaar]

Copy/pasting from the forums can introduce extra white space characters. Jonathans solution works fine. See attached example.

[jagan · Accepted solution]

Hi Rohit,

PFA file.

Regards,

Jagan.

[rohit214]

Hi, Gysbert

thanks for your reply

your solution is not providing me correct solution because for 31-12-2012 its should show me 30-11-2012  value

but it shows me 31-12-2012 value on that day also please take a look

thanks

rohit

[rohit214]

Hi,Jagan

Your solution is perfect

that is what i am looking for

thank you very much

thanks

rohit