Calculate Repeat Repair Within 30 Days

[Sayed_Mannan_Ahmad]

I have a dataset which have three fields

1. asset_serial_no (Not Unique)

2. created_on ( Date Field)

3. job_sheet ( Unique)

I have to count the no. of repeated serial no within 30 days and the condition is as follows:

I have implemented the below mentioned logic:-

test:

Load

 asset_serial_no,

 created_on,

 job_sheet

FROM [file location];

noconcatenate

Final:

Load

*,

IF(asset_serial_no=Previous(asset_serial_no) and

previous(created_on)-created_on<=30,

'repeatRepair','not repearRepair') as Flag;

Load * 

Resident test 

ORDER BY asset_serial_no, created_on desc;

DROP TABLE test;

and then I try to count the asset_serial_no based on the flag that I have created but It is not working.

COUNT({<Flag={'repeatRepair'}>}asset_serial_no)

NOTE:- I want to see the repeat repair based on my year and month selection. Year and month is to be created by using created_on field.

Anyone can suggest me the best way to do this.

I am attaching the sample file for your reference.

Thanks in advance.

[Chanty4u]

Try this

COUNT({<Flag={'repeatRepair'}, YearMonth={"$(=Max(YearMonth))"}>} asset_serial_no)

[Sayed_Mannan_Ahmad]

Thank you for your response @Chanty4u .

It's not working.

I need the year and month selection based on the users choice for this I have created the Year and Month filter pane using created_on field.