Transaction date should be segregate based mov type 542 and 653 .

[mcaahmad93]

Dear All,

I am wondering to do the logic of below table.

Requirement: I have below table based on the table i want to segregate the Trans Date based on mov type 542 and 653 for Emp_id.  Provided the below sample data and with results.

Raw Data
Emp id	SeqID	MatYr	Mov Type	Trans Date	Txn Time
22	3	2019	542	31.08.2019	12:57:21
22	9	2021	653	16.07.2021	13:14:58
25	3	2019	542	31.08.2019	12:57:21
25	7	2020	653	03.03.2020	16:47:28
114	5	2020	542	30.10.2020	09:53:23
114	20	2021	653	02.02.2021	17:24:50
Results
Emp id	542 Date	653 Date	Date diff
22	31-08-2019	16-07-2021	685
25	31-08-2019	03-03-2020	185
114	30-10-2020	02-02-2021	95

I would like to request to provide your logic for this requirement. let me know in case of any clarification in requirement.

Regards,

Irshad Ahmad

[Taoufiq_Zarra]

@mcaahmad93 

in chart you can use :

Dimension Emp id

and in measure :

=Only({<[Mov Type]={'542'}>}[Trans Date])

for 542 Date, and 

=Only({<[Mov Type]={'653'}>}[Trans Date])

 for 653Date, and 

=Only({<[Mov Type]={'653'}>}[Trans Date])-Only({<[Mov Type]={'542'}>}[Trans Date])

for Date diff

the output:

[mcaahmad93]

Dear Taoufiq,

Thank you for your valuable time. I tried your expression and got some output.

But in my project output . we have to take multiple dimension and in a straight table and we have multiple mov_Type but we need to take only 542 and 653 date.

I am trying in script level and getting output like this. but we need each Emp id only one record.

Note:653 date should be greater 542 date.later we will calculate the date difference.

let me in case of any doubt in this.

Regards,

Irshad Ahmad

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[mcaahmad93]

Dear Taoufiq,

please find the sample data with expected output below :

Sample Data:

Emp Id	/BIC/YSERNR	MOV_TYPE	Trans Date
81858	81858	101	2020-09-03
81858	81858	101	2020-11-27
81858	81858	101	2021-01-06
81858	81858	291	2021-02-15
81858	81858	541	2020-12-09
81858	81858	542	2020-12-11
81858	81858	641	2020-09-03
81858	81858	641	2020-11-24
81858	81858	641	2021-01-01
81858	81858	653	2021-01-28
114	184300596	101	2020-09-10
114	184300596	101	2020-10-31
114	184300596	101	2020-11-05
114	184300596	101	2020-11-29
114	184300596	101	2021-01-29
114	184300596	101	2021-01-30
114	184300596	101	2021-02-06
114	184300596	291	2021-01-30
114	184300596	541	2020-09-22
114	184300596	541	2020-12-11
114	184300596	541	2021-02-06
114	184300596	542	2020-10-30
114	184300596	641	2020-09-04
114	184300596	641	2020-10-30
114	184300596	641	2020-11-03
114	184300596	641	2020-11-14
114	184300596	641	2021-01-27
114	184300596	641	2021-01-29
114	184300596	641	2021-02-06
114	184300596	653	2020-08-28
114	184300596	653	2021-02-02

Expected Output:

Emp Id	YSERNR	Date542	Date653	Date diff
81858	81858	2020-12-11	2021-01-28	48
114	184300596	2020-10-30	2021-02-02	95

Note:653 date should be greater than 542 if not then we will not take Emp id of that  just skip.

Regards,

Irshad Ahmad

[mcaahmad93]

Dear All,

Please provide some input on above requirement.

Regards,

Irshad Ahmad