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

[alexnajm]

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

Spoiler

[allwynthomas24]

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:

Spoiler

Batch Macro:

[Qiu]

Spoiler

[ainderike]

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.

[leozhang2work]

Spoiler

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]

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]

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//

[jtleryx]

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]

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]

Fun challenge!