How to generate QuarterEndDate

[grajmca_sgp123]

Hi all,

I have a currency table with QuarterStartDate,Rates , different currency codes.

QuarterStartDate	Rate	Currency
3/1/2015	0.00636	ADP
3/1/2015	105.74	EUR
6/1/2015	0.00674	ADP
6/1/2015	112.17	EUR
9/1/2015	0.00679	ADP
9/1/2015	113.03	EUR

Now My requirement is I want to assign same Rate value from QuarterStartDate to QuarterEndDate by each Currency but we do not have QuarterEndDate.How do we create QuarterEndDate in data model and how can we assign same Rate value by each Currency from .QuarterStartDate to QuarterEndDate .

this is my expected output table:For example Currency is EUR

Dates	Rate	Currency
All Dates From 3/1/2015 - To 5/30/2015	Same Rate Value for all these Dates 105.74	EUR
From 6/1/2015 To 08/31/2015	Same Rate Value for all these Dates 112.17	EUR
From 9/1/2015 To 12/31/2015	113.03	EUR
Same logic for other Currency codes also

Thanks in advance

[PrashantSangle]

Hi,

did you try with QuarterEnd()

Regards,

[Anonymous]

Try this one:

quarterend ( '2005-10-29' ) will return 2005-12-31

[grajmca_sgp123]

but in table we have QuarterStartdates only....we do not have all dates to generate QuarterEnd dates.

[jonathandienst]

One way is to use the start date of the next period. Something like:

LOAD

  QuarterStartDate,

  If(Previous(Currency) = Currency,

    Date(Previous(QuarterStartDate) - 1),

    Today()) As QuarterEndDate,

  Currency,

  Rate

RESIDENT Currencies

ORDER BY Currency, QuarterStartDate DESC;

Now you can interval match the rates to the detail.

[vishsaggi]

Hello Rejesh,

Try QuarterEnd(Date, 0,3) As QuarterEndDate.

So for eg QuarterEnd('06/01/2015', 0,3)  it Returns 08/31/2015.

Please Try and let me know.

Thanks,
V.

[tamilarasu]

Hi Rajesh,

Data:
LOAD * INLINE [
    QuarterStartDate, Rate, Currency
    3/1/2015, 0.00636, ADP
    3/1/2015, 105.74, EUR
    6/1/2015, 0.00674, ADP
    6/1/2015, 112.17, EUR
    9/1/2015, 0.00679, ADP
    9/1/2015, 113.03, EUR
];

Final:
Load
'From ' & QuarterStartDate & ' To ' & Monthend(AddMonths(QuarterStartDate,2)) as Dates,
Rate,
Currency
Resident Data;
Drop Table Data;

Sample attached. Let me know.