Needs to aggregate at the highest level

[clondono]

Hello community,

I have a requirement to create an aging table, the issue is that the same product ID may be sold by different business.  As a result the only way to get the correct results is to include the Product Id (lowest level) in the charts (table 1 and table 2 screenshot).  I need to create a chart that has no product ID as a column (table 3 screenshot), see screenshot below:

When product ID is not included in the chart then the results are not as expected.

This are my formulas for aging:

Aging 0-3:

=If([13WK_Bill]>0 and [13WK_Bill]<= [CurrentInv], [13WK_Bill], if([13WK_Bill]> 0 and [13WK_Bill]> [CurrentInv], [CurrentInv],0))

Aging 4-6:

=if(([CurrentInv]-[Aging0-3]=0) or ([CurrentInv] < ([13WK_Bill] + [26Wk_Bill])), ([CurrentInv]-[Aging0-3]), [26Wk_Bill])

Aging 7-9:

=if(([CurrentInv]-[Aging0-3]-[Aging4-6]= 0) or ([Aging0-3]+[Aging4-6]+([CurrentInv]-[Aging0-3]-[Aging4-6]))< ([13WK_Bill]+[26Wk_Bill]+[39Wk_Bill]),([CurrentInv]-[Aging0-3]-[Aging4-6]), [39Wk_Bill])

Aging 10-12:

=if(([CurrentInv]-[Aging0-3]-[Aging4-6]-[Aging7-9]= 0) or ([Aging0-3]+[Aging4-6]+[Aging7-9]+([CurrentInv]-[Aging0-3]-[Aging4-6]-[Aging7-9]))< ([13WK_Bill]+[26Wk_Bill]+[39Wk_Bill]+[52Wk_Bill]),([CurrentInv]-[Aging0-3]-[Aging4-6]-[Aging7-9]), [52Wk_Bill])

Aging >12:

=[CurrentInv]-[Aging0-3]-[Aging4-6]-[Aging7-9]-[Aging10-12]

Any ideas how to achieve this?

Thanks in advance for your help!

Carlos

[sunny_talwar]

May be Aggr() over the Product dimension

Sum(Aggr(If([13WK_Bill]>0 and [13WK_Bill]<= [CurrentInv], [13WK_Bill], if([13WK_Bill]> 0 and [13WK_Bill]> [CurrentInv], [CurrentInv],0)), Business, ProductID))

[Anonymous]

Your confusion might be whether you're referring to the column name from your straight table or a field name within a table that has the same name.

For example is 'CurrentInv' a field in a table within your data model as well as the name of the column in your straight table?

I think in your Aging expressions you are wanting it to refer to the column name in your straight table.

If so, then you either need to restate the expression you are using for the 'CurrentInv' column(probably sum(CurrentInv) and put it in your aging expressions everywhere that you have [CurrentInv]. 

Or change the name of the column to a name that doesn't exist in the data model lik 'Current Invoices'.

[clondono]

Thank you Sunny.  This works very well for the first expression (aging 0-3), still getting the incorrect results when applying the logic to the other expressions.

[clondono]

Thanks for the reply Wallo, yes I am using the table columns names in the expressions.  I dont have any field names like those in my data model.

[sunny_talwar · Accepted solution]

I saw that you mentioned below that you are using expression labels, but for Aggr() function to work, you will have to replace those labels with the actual expressions. Aggr() function doesn't recognize labels. So, as soon your replace the labels with there actual definition, you should be good to go with all your expressions

[clondono]

Thanks for the explanation Sunny, I did replaced those labels and now the expressions are behaving as expected.