Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Value Month

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?

3 Replies
manojkvrajan
Luminary
Luminary

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;

rajni_batra
Specialist
Specialist

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

Hope it Helps!!

jagan
Luminary Alumni
Luminary Alumni

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.