How to use the IF CLAUSE correctly in this case?

Hello guys.

I am having trouble to create an IF CLAUSE. Basically I have a Dataset which contains financial values of investiment funds, and this dataset is public data, updated every day. (For the example I used 2020 data).

Due to data, I have separed the dates by months using "DateTimeMonth". The issue is that I need to EXCLUDE or SEGREGATTE Investiment Funds that dont have any sort of financial movimentation for 12 months or, for example 3 months.

Example: CNPJ = National Register of Legal entity.

This one "00.822.055/0001-87" only had movement on the months of 4.;6.; > 9. So, it could be excluded.

The problem is that I'm unable to think of a if clause that represents that logic to automate the analysis. Do you guys have any ideas?

Maybe creating a formula that exclude the investiment fund if it does not have movementations for more that 6 months for example..

I can only put 5 archives here in the post, sorry about that. Each archive represents a month

Thank you!

Informe Diario.yxmd

inf_diario_fi_202002.csv

inf_diario_fi_202004.csv

inf_diario_fi_202001.csv

inf_diario_fi_202003.csv

Workflow

Accepted answers

Emil_Kos

Hi @Joker_Hazard,

I have re-read your post. Now I am putting max value for each of the months and afterwards, I am counting them. As you aren't interested in a subsequential static month.

I think this solution is better for you.

Informe Diario updated by Emilv2.yxmd

All comments

Emil_Kos

Hi @Joker_Hazard,

I needed to change your solution as you had a macro in place that didn't export correctly. I have created a solution that excluded every line that was static for 3 months or longer:

If you don't want to exclude just remove the filter tool and you will what is the highest amount of the static month for each fund.

Good luck!

Informe Diario updated by Emil.yxmd

Emil_Kos

Hi @Joker_Hazard,

I have re-read your post. Now I am putting max value for each of the months and afterwards, I am counting them. As you aren't interested in a subsequential static month.

I think this solution is better for you.

Informe Diario updated by Emilv2.yxmd

Joker_Hazard

Awesome solution Emil! Thank you very much!!!

Just a doubt, I see that the formula keeps as "changed" when we have for example:

Months Sum_Capt Status

1 999999 Changed

2 0 Changed

3 999999 Changed

4 0 Changed

5 999999 Changed

So just by curiosity how would you detect those zeros in the formula tool that you used to make the solution?

Tks

Emil_Kos

Hi @Joker_Hazard,

Thanks, I love reactions like this! 🙂

To answer your question they would be counted as changed because the value is changing. Can you show me how this one should be counted in your opinion?

Joker_Hazard

hahahahahha

We could say that they were STATIC as they have a "zero". Maybe STATIC 2.0? 😂

Emil_Kos

Hi @Joker_Hazard,

In that case my solution wasn't working correctly.

Here I was comparing if the previous line is different from the current row. Now I take into consideration 0 as static.

Follow up question to that. Do you take into consideration month as static if they will have at least one static row or all of them should be static? Currently, I am not considering month as static if it will have at least 1 non-static line. This makes sense to me but it is you project so I prefer to ask.

Informe Diario updated by Emilv3.yxmd

Joker_Hazard

Thank you for the extra help!

I have no definite answer for that due to the fact that my project is still in phase of study. So, I'm trying to identify patterns and outliers before starting to create statistical correlations at the very end.

Now I have plenty of material to work on... Once again, thanks for the help 🙂

Cheers from Brazil

Emil_Kos

Hi @Joker_Hazard,

I am happy that I could help. Cheers from Poland!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703