How to use the IF CLAUSE correctly in this case?

[Joker_Hazard]

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!

[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!

[Emil_Kos · Accepted solution]

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.

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

[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!