Corresponding Month Value to column

[Neffri]

Hi all,

Corresponding Date points to a month from As of Date, from which I would take the Denominator (As of Date= Corresponding Date) and put it in the column Corresponding Denominator.

Example here:

I wrote calculation to find correct Corresponding Date but i have problem with assigning the correct value to Corresponding Denominator.

Below sample what I would like to get:

Is it possible to do?

I will be grateful for your help.

[atcodedog05]

Hi @Neffri ,

Just want to clarify

Corresponding Denominator = As of Date- Corresponding Date

Is this the calculation. Your numbers are not matching the calculation.

Can you explain how you are getting 14 for first row so that we can help you further

[Neffri]

Blue row has As of date = '8/31/2020' and Corresponding date = '6/30/2020' that mean I should take the value for Corresponding Denominator from row, where As of date = '6/30/2020'. All the rest columns needs to match the following (department ='A'; Tech Asset Abbreviation = 'Apps'; Tech Asset Prioritization = 'New to Production'; Metric Segment L1 = 'Complete'.

[Jean-Balteryx]

Hi @atcodedog05 ,

I think it would be a join to itself using Corresponding date = As of date and all other fields matching.

Cheers,

Jean-Baptiste

[atcodedog05]

Hi @Neffri ,

After your explanation

Now i have clear understanding here is a workflow

Hope this helps : ) Please check and let me know

If this helps please mark my post as solution.

[atcodedog05]

Yup thats right @Jean-Balteryx  : )

[Neffri]

I've tried this self join before and it doesn't work.

I should receive the same number for denominator when i filter by as of date ='6/30/2020' and corresponding denominator when I filter by as of date '8/31/2020'.  

[atcodedog05]

Hi @Neffri ,

I checked the workflow again. And i seem to be getting the correct output.

Can you check again. Here is the troubleshooting.

Outputs

Please check and let me know. Please provide the the error output scenario.

[Neffri]

Thank you for helping me

Please check the error scenario.

I'm wondering if it's possible to create such column...

[atcodedog05]

Hi @Neffri ,

The output is correct.

If you see below there are few records which has Corresponding date as "2020-06-30" and not "2020-08-31"

Hence you will not get it in row count of As IF "2020-08-31"

Hope this clarifies things

If not please let me know. Happy to help : )