topic Re: One year records in QlikView

One year records

qlikviewwizard — Thu, 01 Oct 2015 06:02:28 GMT

Hi All,

I want to show Sales values as zero for the values of min(Transaction_date) more than 365 days to today's date.

How to achieve this?

Trans_Date Trans_ID Sales

10-June-2014 101 120

21-11-2014 101 130

21-11-2014 220 130

21-10-2014 220 340

In the above scenario, I want show the data as below:

Trans_Date Trans_ID Sales

10-June-2014 101 0

21-11-2014 101 0

21-11-2014 220 130

21-10-2014 220 340

Please help me.

Thanks in advance.

Re: One year records

Kushal_Chawda — Thu, 01 Oct 2015 06:29:43 GMT

try this

Data:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

left join (Data)

Load date(min(Transaction_Date),'DD/MM/YYYY') as Transaction_Date,

Trans_ID,

'1' as MinDateFlag

Resident Data

where num(Transaction_Date)>num(today()-365)

Group by Trans_ID;

Final:

NoConcatenate

LOAD *,

if(MinDateFlag=1,0,Sales) as SalesFinal

Resident Data;

DROP Table Data;

Re: One year records

jagan — Thu, 01 Oct 2015 06:37:17 GMT

Hi,

Try this script

Data:

LOAD

If(Transaction_Date >= date(today()-365,'DD/MM/YYYY'), Sales, 0) AS Sales_Calc;

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

Regards,

Jagan.

Re: One year records

Kushal_Chawda — Thu, 01 Oct 2015 06:53:14 GMT

Data:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

left join (Data)

Load date(min(Transaction_Date),'DD/MM/YYYY') as Transaction_Date,

Trans_ID,

'1' as MinDateFlag

Resident Data

Group by Trans_ID;

Final:

NoConcatenate

LOAD *,

if(MinDateFlag=1 and num(Transaction_Date)>num(Today()-365),0,Sales) as SalesFinal

Resident Data;

DROP Table Data;

Re: One year records

shair_abbas — Thu, 01 Oct 2015 07:03:53 GMT

I think this should work..

Table:

load

Trans_Date,

if((num(today())-num(Trans_Date))>364,0,Trans_ID Sales) as Trans_ID Sales

from Table;

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 07:17:28 GMT

Hi Jagan,condition is when min(Transaction_Date)>=365 then Sales value=0.

Will your logic works?

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 07:46:53 GMT

Hi All,

Both solutions are not working.

min(Trans_Date)>365 then Sales is zero for those Trans_IDs for all the TransactionDates.

Please help me.

Re: One year records

jagan — Thu, 01 Oct 2015 08:12:59 GMT

Hi Arjun,

Try like this

Data_Temp:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

MinDateTemp:

LOAD

Min(Transaction_Date) AS MinDate

RESIDENT Data_Temp;

LET vMinDate = Num(Peek('MinDate'));

DROP TABLE MinDateTemp;

Data:

LOAD

If(Transaction_Date > Date('$(vMinDate)' + 365), Sales, 0) AS Sales_Calc

RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Hope this helps you.

Regards,

Jagan.

Re: One year records

jagan — Thu, 01 Oct 2015 08:14:05 GMT

Used one table to pull min date and stored it in variable and used that variable in the load statement again. Based on your requirement you can change the logic.

Regards,

Jagan.

Re: One year records

jsingh71 — Thu, 01 Oct 2015 08:21:21 GMT

See the attached qvw (Second tab).

Re: One year records

Anonymous — Thu, 01 Oct 2015 08:43:13 GMT

Hi Arjun,

Please find attached file.

Hope it helps!!

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 08:47:08 GMT

Hi Jagan,

Implemented this logic. It is showing Sales values for 2015 from Jan to Sep.

I should show zero or eliminate completely.

Please advise.

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 08:47:54 GMT

Re: One year records

jagan — Thu, 01 Oct 2015 10:06:41 GMT

Hi,

Try this, the min date is 1-Jan-2014 so next 365 days is upto december 2014, this script will have vales for 2014, and shows 0 for 2015.

Data_Temp:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

MinDateTemp:

LOAD

Min(Transaction_Date) AS MinDate

RESIDENT Data_Temp;

LET vMinDate = Num(Peek('MinDate'));

DROP TABLE MinDateTemp;

Data:

LOAD

If(Transaction_Date > Date('$(vMinDate)' + 365), 0, Sales) AS Sales_Calc

RESIDENT Data_Temp;

DROP TABLE Data_Temp;

Re: One year records

jagan — Thu, 01 Oct 2015 10:08:11 GMT

If you want to delete records use below script

Hi,

Try this, the min date is 1-Jan-2014 so next 365 days is upto december 2014, this script will have vales for 2014, and shows 0 for 2015.

Data_Temp:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,1/3/2014,1120.446

Y,712,2/5/2014,547.02

N,715,1/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,1242,9/27/2015,456

Y,1242,9/27/2014,320

Y,1244,9/29/2014,278.794

Y,1242,9/30/2015,124

Y,1242,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,1242,5/30/2015,423

Y,1242,8/30/2015,654

Y,1242,2/28/2015,344

Y,1242,10/30/2014,321

Y,1242,11/30/2014,423

Y,1242,12/30/2014,654

];

MinDateTemp:

LOAD

Min(Transaction_Date) AS MinDate

RESIDENT Data_Temp;

LET vMinDate = Num(Peek('MinDate'));

DROP TABLE MinDateTemp;

Data:

Noconcatenate

LOAD

RESIDENT Data_Temp;

Where Transaction_Date < Date('$(vMinDate)' + 365);

DROP TABLE Data_Temp;

Re: One year records

ahmar811 — Thu, 01 Oct 2015 10:35:20 GMT

try this hope its help you

Table:

load * inline [
Trans_Date,Trans_ID,Sales

10-06-2014,101,120

21-11-2014,101,130

21-11-2014,220,130

21-10-2014,220,340
];

temp:
load * , if(num(Trans_Date)<num(today())-365,'0',Sales) as test Resident Table;
drop table Table;

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 11:47:04 GMT

Still no luck

Re: One year records

qlikviewwizard — Thu, 01 Oct 2015 12:10:53 GMT

Hi Jagan,

Please check this script and data:

I am missing something in fixing the issue.

Data:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,11/3/2014,1120.446

Y,712,11/5/2014,547.02

N,715,10/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,9999,4/25/2014,456

Y,9999,9/27/2014,320

Y,9999,9/29/2014,278.794

Y,9999,9/30/2015,124

Y,9999,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,9999,10/01/2015,423

Y,9999,8/30/2015,654

Y,9999,2/28/2015,344

Y,9999,10/30/2014,321

Y,9999,11/30/2014,423

Y,9999,12/30/2014,654

];

left join (Data)

Net_Sales:

Load date(min(Transaction_Date),'DD/MM/YYYY') as Min_Transaction_Date,(today()-date(min(Transaction_Date),'DD/MM/YYYY')) as Diff,

Trans_ID,Sum(Sales) as Net_Sales

Resident Data

where Flag='Y' //and (today()-date(min(Transaction_Date),'DD/MM/YYYY'))<=365

Group by Trans_ID,Transaction_Date;

Re: One year records

jagan — Thu, 01 Oct 2015 12:22:37 GMT

Hi,

Try this script

Data:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,11/3/2014,1120.446

Y,712,11/5/2014,547.02

N,715,10/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,9999,4/25/2014,456

Y,9999,9/27/2014,320

Y,9999,9/29/2014,278.794

Y,9999,9/30/2015,124

Y,9999,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,9999,10/01/2015,423

Y,9999,8/30/2015,654

Y,9999,2/28/2015,344

Y,9999,10/30/2014,321

Y,9999,11/30/2014,423

Y,9999,12/30/2014,654

];

INNER JOIN(Data)

LOAD

Trans_ID

WHERE MinDate >= Date(Today() - 365);

LOAD

Trans_ID,

Min(Transaction_Date) AS MinDate

RESIDENT Data

GROUP BY Trans_ID;

Re: One year records

jagan — Thu, 01 Oct 2015 12:25:56 GMT

Hi,

If you don't want to remove the records then use below script with flag and use that flag in set analysis

Hi,

Try this script

Data:

load Flag,

Trans_ID,

Sales,

date(date#(Transaction_Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Transaction_Date,

num(Month(date(date#(Transaction_Date,'MM/DD/YYYY')))) as Month,

MonthName(date(date#(Transaction_Date,'MM/DD/YYYY'))) as MonthName,

'Q'&Ceil(num(Month(date(date#(Transaction_Date,'MM/DD/YYYY'))))/3) as Quarter,

Year(date(date#(Transaction_Date,'MM/DD/YYYY'))) as Year inline [

Flag,Trans_ID,Transaction_Date,Sales

Y,1242,1/1/2014,272.76

Y,706,1/4/2015,306.36

Y,708,1/4/2015,700.44

Y,710,2/5/2014,631.7

Y,1242,5/5/2014,631.7

N,710,5/2/2015,947.52

Y,710,1/4/2015,340.56

Y,712,11/3/2014,1120.446

Y,712,11/5/2014,547.02

N,715,10/3/2014,543.328

Y,795,6/2/2015,87.62

Y,801,1/3/2014,376.605

Y,801,6/2/2015,301.284

Y,801,7/5/2015,234.43

Y,1112,9/1/2014,217.1

Y,1112,1/3/2014,189.72

Y,1126,2/5/2014,1283.84

N,1151,1/3/2014,133.11

Y,1242,2/5/2014,388.57

Y,1242,6/2/2015,230.496

Y,1242,1/4/2015,207.12

Y,1244,7/5/2015,208.794

N,1246,1/4/2015,245.61

Y,1248,2/5/2014,826.06

Y,1254,2/5/2014,372.648

Y,1112,9/2/2014,123

N,1112,9/28/2014,143

Y,1112,9/27/2014,217.1

N,1112,9/25/2014,256

Y,1112,9/30/2014,217.1

Y,1112,9/29/2014,60

N,710,5/2/2014,947.52

Y,795,6/2/2014,87.62

Y,801,6/2/2014,301.284

Y,801,7/5/2014,234.43

Y,1242,6/2/2014,230.496

Y,1244,7/5/2014,208.794

Y,1242,9/29/2015,280.496

Y,1242,9/29/2014,320

Y,9999,4/25/2014,456

Y,9999,9/27/2014,320

Y,9999,9/29/2014,278.794

Y,9999,9/30/2015,124

Y,9999,9/30/2014,321

Y,1242,7/30/2015,124

Y,1242,8/30/2014,321

Y,9999,10/01/2015,423

Y,9999,8/30/2015,654

Y,9999,2/28/2015,344

Y,9999,10/30/2014,321

Y,9999,11/30/2014,423

Y,9999,12/30/2014,654

];

LEFT JOIN(Data)

LOAD

Trans_ID,

1 AS Flag

WHERE MinDate >= Date(Today() - 365);

LOAD

Trans_ID,

Min(Transaction_Date) AS MinDate

RESIDENT Data

GROUP BY Trans_ID;

Use below set analysis expression

=Sum({<Flag={1}>} Sales)

Regards,

Jagan.