Previous and Current month data comparison

[#1]

Good day,

I have the below issue:

On the same dataset,

- Previous month, if the class is not equal to 'Legal'

- Current  month, is class is 'Legal'

- then sum(total_limit)

I have compiled a code, but for some reason I dont think I'm getting it right...

Please assist:

[Temp]:

LOAD class,

  card_account_status_code,

  prev_card_account_status,

  Date(MakeDate(left(info_date,4),mid(info_date,5,2),01), 'YYYY/MM/DD') as [Date],

  product,

  total,

  id_acc

From

C:....\Port\Port_*.qvd

(qvd);

[CurrPrevMonth]:

LOAD *

  if(((id_acc = previous(id_acc)) and date(date) <> date(monthstart(date-1) and class = 'Legal'),1,0) as [CurrFlag],

  if(((id_acc = previous(id_acc)) and date(date) <> date(monthstart(date-1) and previous(class) <> 'Legal' and upper(left(previous(card_account_status_code),1)<>'C'),1,0) as [PrevFlag]

Resident Temp

Order by id_acc, Date asc;

DROP Table Temp;

then on the front end i did the following:

sum(total)

[sunny_talwar]

Can you share the data behind this script so that we can test it out?

[Gysbert_Wassenaar]

[Temp]:

LOAD class,

...

Date(MakeDate(left(info_date,4),mid(info_date,5,2),01), 'YYYY/MM/DD') as [Date],

...

[CurrPrevMonth]:

LOAD *

  if(((id_acc = previous(id_acc)) and date(date) <> date(monthstart(date-1) and class = 'Legal'),1,0) as [CurrFlag],

...

Qlikview fields are case sensitive. So Date and date are two different fields to Qlikview.

date(monthstart(date-1) <- is missing a closing parenthesis.

date(monthstart(date-1)) returns the last day of the previous month. Is that what you need? Or do you need the first day of the previous month? That is returned by date(monthstart(date,-1)).

[#4]

Sorry for the late reply, I want the last day of the previous month

[#5]

Hi,

The data behind is huge about 2G and I cant transfer it to the site

[#6]

if vMaxDate is having the current date, then you can use

Date(MakeDate(Year(vMaxDate), num(month(vMaxDate)))-1,'YYYY/MM/DD') will give last date of previous month

and

=Date(MakeDate(Year(vMaxDate), num(month(vMaxDate))+1)-1,'YYYY/MM/DD') will give last date of current month