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.

[Neffri]

You're right, the problem is there.

I'm trying to create a table in such way that for 2020/08/31 I would get the all rows from 2020/06/30.

[atcodedog05 · Accepted solution]

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 : )

[Neffri · Accepted solution]

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 🙂

[atcodedog05]

Hi @Neffri ,

Glad that you were able to fix it by yourself.

Really sorry and sad that i was not able to help you out completely with your requirement.

I think you should go ahead mark your post as Solution.

This was bit complex scenario i guess. But certainly a fun one.

Anyways happy to help : )

[Neffri]

I could've described my issue in more details.

Thanks to you I've manged to figure out my problem 🙂

Your solutunion would be correct if we would like to show the denominator value in 2020/06/30 for the existing categories in 2020/08/31.

Once again thank you for your time and support !!! 🙂

[atcodedog05]

Happy to help : ) @Neffri