Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

One year records

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.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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

20 Replies
Kushal_Chawda

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
Luminary Alumni
Luminary Alumni

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

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
Partner - Creator
Partner - Creator

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
Master II
Master II
Author

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

Will your logic works?

qlikviewwizard
Master II
Master II
Author

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist
Partner - Specialist

See the attached qvw (Second tab).