topic Re: Value Month in QlikView

Value Month

Thu, 16 Aug 2012 19:27:31 GMT

i have table which have 10 yr data for date wise with each date value.

i want to make a table where i want to show value of data only for dast day of each month.

i.e i want to select data of last date of every month means only 12 value per yr.

is it possible?

Re: Value Month

manojkvrajan — Thu, 16 Aug 2012 19:47:36 GMT

Assume the last day of the month's data is available in the Table, we can retrieve the Date information using a Temp table as mentioned below followed by the full load with the EXISTS condition. Does it help?

Temp:

LOAD DISTINCT MonthYear,
DATE(max(Date)) AS Date
FROM Table
GROUP BY MonthYear;

Table:

LOAD
*
FROM
Table
WHERE EXISTS (Date,Date);

DROP TABLE Temp;

Re: Value Month

rajni_batra — Fri, 17 Aug 2012 07:31:09 GMT

use monthend(date) to get the last date of month.....

Hope it Helps!!

Re: Value Month

jagan — Fri, 17 Aug 2012 12:47:23 GMT

Hi,

Try like this

Sales:

LOAD
SalesDate,
Sales
WHERE IsMonthEnd = 1;
LOAD
SalesDate,
If(Floor(Num(SalesDate)) = Floor(Num(MonthEnd(SalesDate))), 1, 0) AS IsMonthEnd,
Sales;
LOAD * INLINE [
    SalesDate, Sales
    1/1/2012, 100
    1/2/2012, 200
    1/3/2012, 300
    1/4/2012, 200
    1/31/2012, 3000
    2/1/2012, 100
    2/2/2012, 200
    2/3/2012, 300
    2/4/2012, 200

    2/29/2012, 5000
    3/1/2012, 100
    3/2/2012, 200
    3/3/2012, 300
    3/4/2012, 200
    3/31/2012, 10000
];

Hope this helps you.

Regards,

Jagan.