Forum Discussion
Matrix with 2 tables
https://advanprocommx-my.sharepoint.com/:u:/g/personal/eduardo_s_advanpro_com_mx/EWXlLcpE75FLhVXhC_B...
Hi Guys, Here is a link, please let me know if you could download it.
I have those 2 tables, The first one have all the accounts, so I put a filter, showing just the ones that have an "N" in the column "Acepta Datos". This is showing the fathers accounts, so I select a Matrix, and what I need is have it on levels, for example, the first level is the first number before the first "-", in this case the first level will have 10 rows (from 0 to 9), the second level should be selecting the "+" in the matrix and a list must go down with the next level, for example, if you select the row of number 1, a list will go down, showing the next level with all the accounts that begin with "1", but before the second "-", etc. All the father accounts with the filter of "N" shouldnt have any amountm but the son accounts have a debit amount, so those should be the sum of each level.
I hope I could explain myself
Kind regards
Eduardo Silva
HI Anonymous,
You can add calculated columns to the second table to expand all levels and use them to create a hierarchy to use on matrix visual.
Steps:
1. Create calculated columns:
P0 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 1 ) P1 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 2 ) P2 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 3 ) P3 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 4 ) P4 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 5 )2. Create a matrix with the hierarchy field and value field(aggregate mode: sum)
3. Add 'table 1' [Acepta Datos] fields to the matxi visual level filter to filter records with 'N' tags.
Results:
Notice: your value fields seem saved as text type, you need to modify your value fields type to 'decimal number' to unlock the common aggregate features.
Regards,
Xiaoxin Sheng
1 Reply
HI Anonymous,
You can add calculated columns to the second table to expand all levels and use them to create a hierarchy to use on matrix visual.
Steps:
1. Create calculated columns:
P0 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 1 ) P1 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 2 ) P2 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 3 ) P3 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 4 ) P4 = PATHITEM ( SUBSTITUTE ( [CTA_CONTABLE son], "-", "|" ), 5 )2. Create a matrix with the hierarchy field and value field(aggregate mode: sum)
3. Add 'table 1' [Acepta Datos] fields to the matxi visual level filter to filter records with 'N' tags.
Results:
Notice: your value fields seem saved as text type, you need to modify your value fields type to 'decimal number' to unlock the common aggregate features.
Regards,
Xiaoxin Sheng