topic Re: Needs to aggregate at the highest level in QlikView

Needs to aggregate at the highest level

clondono — Wed, 18 Jan 2017 21:10:50 GMT

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

Re: Needs to aggregate at the highest level

sunny_talwar — Wed, 18 Jan 2017 21:58:25 GMT

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))

Re: Needs to aggregate at the highest level

Anonymous — Wed, 18 Jan 2017 22:50:42 GMT

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'.

Re: Needs to aggregate at the highest level

clondono — Thu, 19 Jan 2017 04:28:14 GMT

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.

Re: Needs to aggregate at the highest level

clondono — Thu, 19 Jan 2017 04:29:36 GMT

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.

Re: Needs to aggregate at the highest level

sunny_talwar — Thu, 19 Jan 2017 10:13:40 GMT

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

Re: Needs to aggregate at the highest level

clondono — Thu, 19 Jan 2017 15:16:58 GMT

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