Cross Tab the value by each line

Question

Hi Community,

I would like to ask on the way that we can summarize the information by each line items. In the screenshot below, the result that at the ageing bracket >360 showing the final value of invoice 434.45 (=  9 + 426). It is because i summarize the invoice as per second screenshot. May i know if we able to produce the result as per third screenshot ?

I also have attached the workflow below for the reference and below is the screenshot of the cross tab tool that i wish to amend.

Back up AR 4.1.yxzp

SH_94 · Answer

Hi @ken_yap_au ,

It looks like does not work in this case as the number of invoice will be duplicated again. Kindly refer to the screen below for your reference.

Screenshot below is the desired result that i would like to have.Could you share your workflow for the reference?

Thank you.

ken_yap_au · Answer

You can make do (somewhat of a workaround) if you also add "Currency" and "Translated Amount" into the Group by, but this will act as a proxy for the Invoice Line ID and can potentially have issues (for instance, if you have two different lines in the same invoice ID for the same currency and same translated amount, it will still be added together because not enough information is provided to indicate they belong to different lines).

SH_94 · Answer

Hi @ken_yap_au

Thank you for the prompt response. I did not have the Invoice Line Number (or other indicator) in this case. May i know if there is alternative way for this?

I will try to look for other solutions as well to fix this issue.

Thank you.