One year records

[qlikviewwizard]

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 · Accepted solution]

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;