Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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
can you share a sample data and the expected output ?
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
Dear All,
Please provide some input on above requirement.
Regards,
Irshad Ahmad