Previous 3 Months Record On Selection

[er_mohit]

Hiiiiii. Good Morning

i m facing a problem  to get the previous 3 month records on the basis of master calendar see below whai i have

is

PP:

LOAD date(Date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') AS DATE,* INLINE [

    Mat Code, Date, SO Qty, Bill Qty

    101, 01-04-2013, 10, 10

    102, 01-05-2013, 20, 20

    103, 01-06-2013, 30, 30

    104, 01-07-2013, 40, 40

    105, 06-07-2013, 10, 10

    101, 02-04-2013, 20, 20

    101, 15-04-2013, 30, 30

    102, 05-05-2013, 40, 40

    103, 06-06-2013, 10, 10

    103, 07-06-2013, 20, 20

];

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() -1 AS DateNumber ,

Date($(vDateMin) + RowNo() -1) AS TempDate

AUTOGENERATE $(vDateToday)-$(vDateMin);

MasterCalendar:

LOAD

Date(TempDate,'DD-MM-YYYY') AS DATE,

MONTH(TempDate) as Month,

Month(TempDate) & ' '& Right(Year(TempDate),2) as [MMM YY]

RESIDENT TempCalendar

//Where TempDate>01-01-2007

ORDER BY TempDate ASC;

As Above i linked my DATE field to Master Date i.e DATE

now,

what i want when i made a selection  on Oct 2013 it gives the Data Of previous 3 months like July,Aug,Sep Of same Year

but i didn't get it

output when i select Month Oct 2013 in pivot or straight table

Mat Code sum(SO Qty) sum( Bill Qty)

104                40                      40    

    105             10                      10

when i Select July 2013 then

Mat Code sum(SO Qty) sum( Bill Qty)

101                 60                             60   apirl month

102                  60                              60 may month

103                  60                              60 june month

See the attached file

[jagan · Accepted solution]

Hi Mohit,

Try this expression

= Only({<DATE={'>=$(=Monthstart(max(DATE), -3))<$(=Monthstart(max(DATE), -1))'},[MMM YY]=>}[Mat Code])

Your variable vendcf if having wrong date.

For Sep and Aug you dont have data.

Regards,

Jagan.

[#3]

Hi,

Try as below:

sum({1<DATE = {'>$(=AddMonths(MAX(DATE),-4)) <=$(=AddMonths(MAX(DATE), -1))'}>}  [SO Qty])

sum({1<DATE = {'>$(=AddMonths(MAX(DATE),-4)) <=$(=AddMonths(MAX(DATE), -1))'}>}  [Bill Qty])

I think, we should take a full set of all records in SET and then we should go for filter.

[#4]

Hi Mohit,

     You can use this for calculate rolling n-period totals:

   rangesum(above(sum({<Month=>}[SO Qty]),1,3))

Below the link is a post about Rolling n-period total/avg, that will be usefull for you i think:

http://community.qlik.com/docs/DOC-4252

Regards,

Zhou

[er_mohit]

Thanku Sir. This is what i want. Now it works fine

m using Date of transaction table instead of master

lovely