How to find Repeated Product ID's??

[#1]

Dear Friends,

I wanted to see any Product ID in last 15 days closed call report is repeated in current day logged report. To be specific, any Product ID which is closed in last 15 days is repeated in the current day again or not. I wanted to count the number of Product ID repeated in current day report.

Repeated Product ID = X

Repeated Product ID % = X/Current day logged calls (X/14)

Please find the  sample table of data below. How to do this in Qlikview... Please help me out

I have written below code...

Current day code: Count({< Calendar_Date = {'=$(=Date(vEndDate))'},Calendar_Month=,[Calendar_Day]=>} Product_Id)

Laste 15 days Code : Count({< Calendar_Date = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'},Calendar_Month=,[Calendar_Day]=>} Product_Id)

Product ID	Call No	Call Closed Date	Last 15 Days Total Closed Calls
			9
QB123	701234	04/17/2014	1
CB123	706781	04/28/2014	1
QS123	7003452	04/24/2014	1
QB125	705234	04/18/2014	1
CB126	74902	04/21/2014	1
QB124	77642	04/30/2014	1
QB125	78643	04/26/2014	1
VS123	768002	04/28/2014	1
ES123	76424	04/25/2014	1

Product ID	Call No	Call Created Date (Current Day)	Current Day Total Created Calls
			14
QB123	7006052914	04/30/2014	1
QB124	7006054397	04/30/2014	1
VS123	7006054487	04/30/2014	1
LF34	7006051286	04/30/2014	1
L9LF	7006054475	04/30/2014	1
WXY	7006053154	04/30/2014	1
ABC	7006054110	04/30/2014	1
1THDB	7006052909	04/30/2014	1
NY64	7006050393	04/30/2014	1
KJNSX	7006050379	04/30/2014	1
NJSX	7006050375	04/30/2014	1
LJKNX	7006053091	04/30/2014	1

Here QB123, QB124 and VS123 are REPEATED. Now the calculation should be (3/14) *100

Please help me to do this in Qlikview.

[Gysbert_Wassenaar]

See attached qvw

[sujeetsingh]

You can achieve this in two ways.

1) Use group by and count(ID) and then you can get those with count >=2

2) Use peek and check that previous ID matches the next

[#4]

Hi Gysbert,

I am writing Call Created Current Day as variableMaxDate =Max(Date) .

Count({

“Whatever the mind can conceive and believe,it can achieve”

[#5]

Hi Gysbert,

I am writing Call Created Current Day as variableMaxDate =Max(Date) .

variableMaxDate= is a variable that i had created in my dashboard.

Product ID and these  dates are coming from different tables

Count({< Calendar_Date = {'=$(=Date(variableMaxDate))'}>} Product ID))

In the given expression by you, how can  I include my variable ??

Coz, Call Created Date (Current Day) = Calendar _Date = {'=$(=Date(variableMaxDate))'}

num(count({<[Call Created Date (Current Day)]={'*'}, 

1. num(count({<Calendar _Date = {'=$(=Date(variableMaxDate))'} = {'*'}, Product ID=p({<Call_Date{'>=$(=Date(variableLast15DaysDate))<=$(=Date(variableMaxDate))'} ={'*'}>}Product ID)>}distinct Product ID)

……………………………..this is not working

[Gysbert_Wassenaar]

Can you post a small qlikview document with all the relevant tables and data? This document can explain how: Preparing examples for Upload - Reduction and Data Scrambling

[ali_hijazi]

use this in your expression

if (Count(ID)>1, Count(ID))

and add the desired dimension in a pivot table

[perumal_41]

Try thies expression

Repated Product : Count({< Calendar_Date = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'},Calendar_Month=,[Calendar_Day]=,Product_Id={"=Count({< Calendar_Date = {'=$(=Date(vEndDate))'},Calendar_Month=,[Calendar_Day]=>} Product_Id)>=1"} >} Product_Id)