topic Re: Previous and Current month data comparison in QlikView

Previous and Current month data comparison

Wed, 02 Dec 2015 18:42:22 GMT

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)

Re: Previous and Current month data comparison

sunny_talwar — Wed, 02 Dec 2015 18:57:21 GMT

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

Re: Previous and Current month data comparison

Gysbert_Wassenaar — Thu, 03 Dec 2015 06:19:10 GMT

[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)).

Re: Previous and Current month data comparison

Fri, 04 Dec 2015 06:23:46 GMT

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

Re: Previous and Current month data comparison

Fri, 04 Dec 2015 06:29:22 GMT

Hi,

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

Re: Previous and Current month data comparison

Fri, 04 Dec 2015 17:03:18 GMT

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