Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Create field by peeking the last value of Every month .

I have data like this:

Value       date

120           1/1/2013

12              31/1/2013

122           31/1/2013

123             1/2/2013

132            15/2/2013

234            30/2/2013

13             30/2/2013

144           30/2/2013

126             1/3/2013

13                31/3/2013

313               31/3/2013..

Now i need to create a field in back end which shows me values ie.. 122, 144,313. which are the last value of the month.Please help me ...

4 Replies
Highlighted
Not applicable

Re: Create field by peeking the last value of Every month .

hi

try this

LOAD

LastValue(Value) as lastvalue,

month

Group by month;

tab1:

LOAD  *, date(Date#(date,'DD/MM/YYYY'),'DD-MM-YYYY') as date_new ,

month(date(Date#(date,'DD/MM/YYYY'),'DD-MM-YYYY')) as month

INLINE [

    Value, date

    120, 1/1/2013

    12, 31/1/2013

    122, 31/1/2013

    123, 1/2/2013

    132, 15/2/2013

    234, 28/2/2013

    13, 28/2/2013

    144, 28/2/2013

    126, 1/3/2013

    13, 31/3/2013

    313, 31/3/2013

]  ;

Highlighted
Not applicable

Re: Create field by peeking the last value of Every month .

hi

the month of feb either 28 days or 29 days.

Highlighted
preminqlik
Valued Contributor II

Re: Create field by peeking the last value of Every month .

Do Group by Year too for Different years

Highlighted
ashwanin
Valued Contributor

Re: Create field by peeking the last value of Every month .

Use function

floor(MonthEnd(date)) as Monthend_Date  in the script

you will get the month end dates and data in the background.