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

[#1]

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.

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

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

date	Price
31/01/2011	0.86
28/02/2011	0.865
29/03/2011	0.869

Thanks,

Qaiyum

[Sokkorn]

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

[#3]

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;

[#4]

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]

Hi Qaiyum,

What about my script above? Did you try yet?

Regards,

Sokkorn Cheav

[#6]

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 · Accepted solution]

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

[#8]

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