We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
2022.1.1.30569 Patch Release Update

The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs. If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.

Weekly Challenge

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

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

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

alexnajm
9 - Comet

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
19 - Altair
19 - Altair
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
8 - Asteroid
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!