Solved: One year records - Page 2 - Qlik Community

qlikviewwizard · ‎2015-10-01

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.

Anonymous · ‎2015-10-01

Hi Arjun,

Please find attached file.

Hope it helps!!

qlikviewwizard · ‎2015-10-01

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.

qlikviewwizard · ‎2015-10-01

jagan · ‎2015-10-01

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;

jagan · ‎2015-10-01

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;

ahmar811 · ‎2015-10-01

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;

qlikviewwizard · ‎2015-10-01

Still no luck

qlikviewwizard · ‎2015-10-01

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;

jagan · ‎2015-10-01

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;

jagan · ‎2015-10-01

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.

One year records

Related Topics