Cross Tab the value by each line

[SH_94]

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.

[ken_yap_au]

I believe you will need to group by the same level of granularity to get the outcome you are expecting; and in this case Group by both Invoice ID and Invoice Line ID. At the moment Alteryx is performing the Crosstab at the Invoice ID level, and hence combines both amounts on the same Invoice ID.

[SH_94]

Hi @ken_yap_au ,

Do you mind to show the thing that i need to amend?

As i try and couldn't get the result based on what u mentioned. Maybe i misunderstood your meaning.

Thank you

[ken_yap_au]

In the Crosstab tool, you have ticked Group by "Invoice ID".

Therefore any calculations are aggregated up to the Invoice ID Level.

Eg. Invoice 1 Line 1 = $20

      Invoice 1 Line 2 = $50

Crosstab on Invoice 1 = $70

You need to Group by Both Invoice ID and another field which represents Invoice Line Number (assuming that fields exists), to then have each calculation aggregated to the Invoice ID and Invoice Line Level.

[SH_94]

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.

[ken_yap_au]

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]

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.