Corresponding Month Value to column

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.

dummy data v1.xlsx

Accepted answers

atcodedog05

Hi @Neffri ,

If you look closer the output meets your condition.

If you filter the As of Date on 2020-08-31 you get only 2020-06-30 as Corresponding date.

Check this part.

The data issue is for as of dates 2020-06-30 and 2019-12-31 corresponding date is same. I think since its a starting date same date is considered for corresponding date.

Please take a close look at the data scenario outputs and all other outputs. If you are still not convince or feel there is a miss. Please tag my name reply on this post.

I will definitely try my best to help you out : )

Corresponding date (3).yxmd

Neffri

O,k I will try to explain what I'd like to achieve in a different way 🙂

Please imagine that you want to show for the newest 2020/08/31 data, historical data from 2020/06/30.

Problem happens when for the 2020/08/31 category which is in the 2020/06/30 doesn't exist ...

because I have to show that for 2020/08/31 we don't have value (denominator =0) for:

department C
Apps
Tranche 0 2019
Not Complete

but in the 2020/06/30 for these categories we had denominator =2.

I have to get this;

I've managed to create workflow, that I think would fulfill my requirements (please check my attachment) 🙂

I've created the Cartesian product of all categories so I will not lose any of rows when I will be joining the tables.

Thank you so much for your help 🙂

Corresponding Date new calc.yxmd

All comments

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'.

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.

Corresponding date.yxmd

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335