Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Srinivas
Contributor

Want to count the custIDs in last three months

Hi All,

I have data like custdids , date, sales and Prdids so on.., this one custids may be repeated or may not in last three months if repeated we need to count how many times it is repeating.

based on above senario we need to display one custid how many times repeated in last three months.

Data is like below:

    

CustIDDate SalesProdID
123501-01-20181005321
123601-02-20182005322
123702-03-20183005323
123803-04-20184005324
123904-05-20185005325
123501-01-20186006321
123601-02-20187006322
123902-03-20188006323
124503-04-20189005321
124604-05-201810005322
123501-01-201811005323
124801-02-201812005324
123904-05-201813005325

Output like below:

    

ProdIDSalesDate CustIDCount
532110001-01-201812351
532220001-02-201812361
532330002-03-201812371
532440003-04-201812381
532550004-05-201812391
632160001-01-201812352
632270001-02-201812362
632380002-03-201812392
532190003-04-201812451
5322100004-05-201812461
5323110001-01-201812353
5324120001-02-201812481
5325130004-05-201812392

Thanks,

Srinivas

Thanks,
Srinivas
1 Solution

Accepted Solutions
sibin_jacob
Contributor III

Re: Want to count the custIDs in last three months

Please use the below script

Test:

load *,1 as Count;

Load * inline [

CustID,Date,Sales,ProdID

1235,01-01-2018,100,5321

1236,1-02-2018,200,53221

1237,02-03-2018,300,5323

1238,03-04-2018,400,5324

1239,04-05-2018,500,5325

1235,01-01-2018,600,6321

1236,01-02-2018,700,6322

1239,02-03-2018,800,6323

1245,03-04-2018,900,5321

1246,04-05-2018,1000,5322

1235,01-01-2018,1100,5323

1248,01-02-2018,1200,5324

1239,04-05-2018,1300,5325

];

Test1:

NoConcatenate

Load CustID,Date,Sales,ProdID,if(CustID=Previous(CustID),Peek('Count')+1,Count) as Count Resident Test

Order by CustID;

DROP table Test;

2 Replies
sibin_jacob
Contributor III

Re: Want to count the custIDs in last three months

Please use the below script

Test:

load *,1 as Count;

Load * inline [

CustID,Date,Sales,ProdID

1235,01-01-2018,100,5321

1236,1-02-2018,200,53221

1237,02-03-2018,300,5323

1238,03-04-2018,400,5324

1239,04-05-2018,500,5325

1235,01-01-2018,600,6321

1236,01-02-2018,700,6322

1239,02-03-2018,800,6323

1245,03-04-2018,900,5321

1246,04-05-2018,1000,5322

1235,01-01-2018,1100,5323

1248,01-02-2018,1200,5324

1239,04-05-2018,1300,5325

];

Test1:

NoConcatenate

Load CustID,Date,Sales,ProdID,if(CustID=Previous(CustID),Peek('Count')+1,Count) as Count Resident Test

Order by CustID;

DROP table Test;

Srinivas
Contributor

Re: Want to count the custIDs in last three months

Thanks Jacob,

It is more help full, Thank you once again.

Thanks,

Srinivas

Thanks,
Srinivas