Query only the past 12 months of data

[Anonymous]

I have the below backend script but would like to add in a rolling 12 month pull for column StartDate in the Where clause. I would imagine this would be fairly easy but can't figure it out. Any help would be appreciated.

***Only extract 12 months of data. Take current date minus 12 months.

AvgDays:

LOAD

CLT_ AS CLTID,
Avg([FIRST_DAYS])AS Avg_Days

FROM $(vPath_QVDs)MainTable.qvd(qvd)

Where NOT IsNull(FIRST_DAYS)
AND REASSIGNED  = 'NO'

GROUP BY
CLT

[Sergey_Shuklin]

Hello, John!

Can you, please, explain what it's mean "rolling 12 month pull"? You need to iterate this script twelve times? Or calculate a total sum for year? Or smth else?

[Anonymous]

I have updated my title.

I only want to pull 12 months of data. Rolling from today's date. Something like below in Oracle.

AND STARTDATE > add_months(TRUNC(SYSDATE), -12) 

[Anil_Babu_Samineni]

Is this you are doing in Qlik / Oracle?

In Qlik, May be this?

Sum({<STARTDATE = {'>= $(=Date(AddMonths(Max(SYSDATE),-12),'DateFormatHere') <=$(=Date(Today(),'DateFormatHere'))'}>}Sales)

[trdandamudi]

See if the below link helps...

How to do Rolling 12 months for latest 12 months with Two Dimensions:

[Anonymous]

MY QLIKVIEW SCRIPT BELOW. NOW I WOULD LIKE TO ADD A FILTER THAT ONLY RETURN THE PAST 12 MONTHS OF DATA FROM A COLUMN CALLED STARTDATE

LOAD

CLT_ AS CLTID,
Avg([FIRST_DAYS])AS Avg_Days

FROM $(vPath_QVDs)MainTable.qvd(qvd)

Where NOT IsNull(FIRST_DAYS)
AND REASSIGNED  = 'NO'

GROUP BY
CLT

[Sergey_Shuklin]

Usually helps this filter:

Where StartDate>Addmonths(today(),-12)

[Digvijay_Singh · Accepted solution]

May be like this--

LOAD

CLT_ AS CLTID,
Avg([FIRST_DAYS])AS Avg_Days

FROM $(vPath_QVDs)MainTable.qvd(qvd)

Where NOT IsNull(FIRST_DAYS)
AND REASSIGNED  = 'NO'

AND StartDate>Addmonths(Today(1),-12)

GROUP BY
CLT