topic Re: How to generate QuarterEndDate in QlikView

How to generate QuarterEndDate

grajmca_sgp123 — Wed, 04 Nov 2015 07:59:17 GMT

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

Re: How to generate QuarterEndDate

PrashantSangle — Wed, 04 Nov 2015 08:04:03 GMT

Hi,

did you try with QuarterEnd()

Regards,

Re: How to generate QuarterEndDate

Anonymous — Wed, 04 Nov 2015 08:07:40 GMT

Try this one:

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

Re: How to generate QuarterEndDate

grajmca_sgp123 — Wed, 04 Nov 2015 08:07:52 GMT

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

Re: How to generate QuarterEndDate

jonathandienst — Wed, 04 Nov 2015 08:08:03 GMT

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.

Re: How to generate QuarterEndDate

vishsaggi — Wed, 04 Nov 2015 08:10:38 GMT

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.

Re: How to generate QuarterEndDate

tamilarasu — Wed, 04 Nov 2015 08:24:45 GMT

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.