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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Cross Tab the value by each line

SH_94
11 - Bolide

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 ?

 

Jacob_66_3-1616920492563.png

 

 

Jacob_66_1-1616920246062.png

Jacob_66_4-1616920666383.png

 

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.

Jacob_66_5-1616921149590.png

 

 

 

6 REPLIES 6
ken_yap_au
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

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
11 - Bolide

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
8 - Asteroid

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
11 - Bolide

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.

 

Jacob_66_0-1616932417284.png

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

Jacob_66_1-1616932530627.png

 

Thank you.

 

Labels