Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

how to to get the values for last working day of each month

Hi,

I am newbie to qlikview and started to create some basic stuffs in qliekiew desktop personal edition. I am in the process of getting values of last working day of each month. Here is the sample data on which i am working.

datePrice
04/01/20110.85
31/01/20110.86
01/02/20110.861
28/02/20110.865
01/03/20110.87
29/03/20110.869

I am getting this data as chart of type Straight table. One of the dimension is to get the last working date and i am able to retreive this using the following formula.

          aggr(max(date),MonthName) ..MonthName is a calculated field in the load script.

Please help me out in getting the prices of the corresponding last working date. the result i am expecting is

datePrice
31/01/20110.86
28/02/20110.865
29/03/20110.869

Thanks,

Qaiyum

1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: how to to get the values for last working day of each month

Hi Qaiyum,

Load script like this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Data]:

LOAD * INLINE [

date,    Price

04-01-2011,    0.85

31-01-2011,    0.86

01-02-2011,    0.861

28-02-2011,    0.865

01-03-2011,    0.87

29-03-2011,    0.869];

[Data1]:

LOAD

    date        AS Date1,

    month(date)    AS Months1,

    Price        AS Price2

RESIDENT [Data];

Create Straight Table with

     =AGGR(Date(MAX(Date1)),Months1)          as Calculated Dimension

     =FirstSortedValue(Price2,-Date1)               as Expression

Hope this help.

Regards,

Sokkorn Cheav

7 Replies
Sokkorn
Honored Contributor

Re: how to to get the values for last working day of each month

Hi Qaiyum,

Let try this script

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Data]:

LOAD * INLINE [

date,    Price

04-01-2011,    0.85

31-01-2011,    0.86

01-02-2011,    0.861

28-02-2011,    0.865

01-03-2011,    0.87

29-03-2011,    0.869];

[Data1]:

LOAD

    date        AS Date1,

    month(date)    AS Months1,

    Price        AS Price2

RESIDENT [Data];

[Data2]:

INNER JOIN ([Data1])

LOAD

    DATE(MAX(Date1))    AS Date1,

    Months1

RESIDENT [Data1] GROUP BY Months1;

Let me know if this one help you.

Regards,

Sokkorn Cheav

Not applicable

how to to get the values for last working day of each month

Cal:

Load

Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD') AS Date,

Year(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD')) AS Year,

Month(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD')) AS Month,

MonthStart(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD')) AS MonthStartDate,

Date(IF(WeekDay(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD')))='Sun',Date(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD'))-2,'DD-MM-YYYY'),

IF(WeekDay(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD')))='Sat',Date(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD'))-1,'DD-MM-YYYY'),

Date(MonthEnd(MakeDate(2011,1,1)+RecNO()-1)))),'DD-MM-YYYY') As [Last Working Day of Month],

(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD'))) AS [Last Day of Month],

WeekDay(MonthEnd(Date(MakeDate(2011,1,1)+RecNO()-1,'YYYY-MM-DD'))) AS WeekDay

  Autogenerate 750;

Not applicable

how to to get the values for last working day of each month

Hi,

I am looking for the 2nd dimension for the price which should be the corresponding price for last working date of each month...

Sokkorn
Honored Contributor

how to to get the values for last working day of each month

Hi Qaiyum,

What about my script above? Did you try yet?

Regards,

Sokkorn Cheav

Not applicable

how to to get the values for last working day of each month

Hi Sokkorn,

It works as the data is required. Thanks for it.

but i am looking at how to write the formula as dimension for this. this will help me in learning some technic of writing the formulas.

Sokkorn
Honored Contributor

Re: how to to get the values for last working day of each month

Hi Qaiyum,

Load script like this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Data]:

LOAD * INLINE [

date,    Price

04-01-2011,    0.85

31-01-2011,    0.86

01-02-2011,    0.861

28-02-2011,    0.865

01-03-2011,    0.87

29-03-2011,    0.869];

[Data1]:

LOAD

    date        AS Date1,

    month(date)    AS Months1,

    Price        AS Price2

RESIDENT [Data];

Create Straight Table with

     =AGGR(Date(MAX(Date1)),Months1)          as Calculated Dimension

     =FirstSortedValue(Price2,-Date1)               as Expression

Hope this help.

Regards,

Sokkorn Cheav

Not applicable

how to to get the values for last working day of each month

Thanks for the help Sokkorn..it is working fine...