Solved: Re: One year records - 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.

jagan · ‎2015-10-12

Hi,

Check this

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,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(Temp)

LOAD

Trans_ID,

Date(Min(Transaction_Date)) AS MinDate

RESIDENT Temp

GROUP BY Trans_ID;

Data:

LOAD

*,

If(Transaction_Date <= Date(MinDate + 365), Sales, 0) AS Sales_CALC

RESIDENT Temp;

DROP TABLE Temp;

View solution in original post

Kushal_Chawda · ‎2015-10-01

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;

jagan · ‎2015-10-01

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.

Kushal_Chawda · ‎2015-10-01

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;

shair_abbas · ‎2015-10-01

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;

qlikviewwizard · ‎2015-10-01

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

Will your logic works?

qlikviewwizard · ‎2015-10-01

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.

jagan · ‎2015-10-01

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.

jagan · ‎2015-10-01

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.

jsingh71 · ‎2015-10-01

See the attached qvw (Second tab).