Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #304: International Bank of Awesome Data - Part III

alexnajm
16 - Nebula
16 - Nebula

Attached solution - not sure why my numbers would be slightly different

Spoiler
Challenge 304 2.PNGChallenge 304 1.PNG
allwynthomas24
11 - Bolide

This was definitely Not an Easy one.. Needed a good brainstorming to get the desired output.. Thank you very much for this challenge @NicoleJohnson as it helped me to understand some tools in greater detail. It literally gave an overview of the challenges a data analyst could face in the real world environment with tight deadlines.

 

Spoiler
Workflow:
allwynthomas24_6-1643828863613.png
Spoiler
Batch Macro:
allwynthomas24_2-1643828115248.png

 

Qiu
20 - Arcturus
20 - Arcturus
Spoiler
Challenge 304.PNG
ainderike
8 - Asteroid

Wow, what a challenge! It took me a while to figure it out.

Also, somehow I got slightly different results and I'm not sure why this is the case. For example, I don't really know why Tim Mayer was assigned the branch 21634 (region C), because based on the provided mapping it should be attributed to Aldert McEachern. I also got different debit/credit balances and average daily balances. I haven't reviewed the solution yet so perhaps it was explained there. When I reconciled my results back to original branch ID, opening balance, transaction amount and client count there were no issues. Anyway, I still decided to post my solution.

 

PS: I loved the Office reference, by the way :)

 

Spoiler
I wasn't able to find an answer to why the macro doesn't output empty .yxdb files for those Assistants with no records. I will probably post it later on the community.

Macro.PNGWorkflow.PNG
Results.PNG
LiuZhang
9 - Comet
Spoiler
304 - 1.png304.png

Took quite a while to match the results, thought it still won't. (Debit/Credits don't match)

The daily average is the hardest to think, as the opening balance used future date's value to find it's, while ending balance does not, it made it hard to understand the correct order to calculate everything. The answer doesn't seem to consider if a customer has multiple transaction within the month, that will affect the sum of opening balance.

kelvinkung
5 - Atom

The "daily balance" calculation in the Macro in the solution file doesn't seem correct, daily balance is the closing balance for the day, and for the first date of each month, if it just takes the opening balance of the first date of the month, then it'd have ignored the transaction amount on that day. Am I right?

kelvinkung
5 - Atom

Just noticed another thing wrong with the solution, when put to the test the macro gets the daily balance wrong for branches where one assistant reports for multiple branches. 

 

The Regex is written in such a way that it will only take the opening balance if the cell above is null, but only the first row will have null value for daily balance, however, each branch should actually take its respective opening balance on the first day for the calculation to be valid.

 

//If isnull([Row-1:Daily Balance]) Then [Opening Balance]
Else [Row-1:Daily Balance]+[Daily Amount] Endif//

 

kelvinkung_0-1645005146399.png

 

jtleryx
5 - Atom

In the solution Macro, the expression in the Multi-Row Formula tool:

 

If isnull([Row-1:Daily Balance]) Then [Opening Balance]
Else [Row-1:Daily Balance]+[Daily Amount] Endif

 

should be:

 

IF [Row-1:Daily Balance] = Null()
THEN [Opening Balance] + [Transaction Amount]
ELSE [Row-1:Daily Balance] + [Transaction Amount]
ENDIF

kelvinkung
5 - Atom

Although I'd realised the condition "[Row-1:Daily Balance] = Null()" is still not a fit for purpose condition; I think it's better top use below conditions, which can guarantee success.

 

//If [Date] = DateTimeFormat(DateTimeTrim([Date],"month"),"%Y-%m-%d")
Then [Opening Balance]+[Daily Amount]
Else [Row-1:Daily Balance]+[Daily Amount] Endif//

kunderwood
8 - Asteroid

Fun challenge!