topic Re: TOTAL Column is not populating - AGGR Issue in QlikView

TOTAL Column is not populating - AGGR Issue

Thu, 21 May 2015 23:35:38 GMT

Hi Friends,

This expression not able to populates the TOTAL amount, can any one help me..

if(FILE_STATUS='OPEN',sum( {<[%GL_EFF_DT]={'>=$(vBillStartDt)<=$(vBillEndDt)'}>}ITEM_AMT),

sum({<[%REMOVAL_DT]={'>=$(vBillStartDt)<=$(vBillEndDt)'}>}ITEM_AMT))

Re: TOTAL Column is not populating - AGGR Issue

Anonymous — Thu, 21 May 2015 23:47:05 GMT

Try this:

sum(aggr(<your expression above here>, dimension1, dimension2...))

where dimensions1, 2, etc. are your chart dimensions

Re: TOTAL Column is not populating - AGGR Issue

Fri, 22 May 2015 00:49:58 GMT

try your same expression but use double quotes for your range values

if(FILE_STATUS='OPEN',sum( {<[%GL_EFF_DT]={">=$(vBillStartDt)<=$(vBillEndDt)"}>}ITEM_AMT),

sum({<[%REMOVAL_DT]={">=$(vBillStartDt)<=$(vBillEndDt)"}>}ITEM_AMT))

Re: TOTAL Column is not populating - AGGR Issue

ToniKautto — Fri, 22 May 2015 01:49:29 GMT

Dimension are always relevant in order to understand how the aggregation is done.

You need to start by making sure that FILE_STATUS='OPEN' returns the value you expect. In a straight table add FILE_STATUS as an expression, and check that it returns OPEN on the rows where you expect it.

Split your expressions is separate chart expressions. Now you should have 4 expression in the chart. Does all these add up, or where is the mismatch? Can you provide a sample QVW file?

Re: TOTAL Column is not populating - AGGR Issue

Fri, 22 May 2015 14:40:35 GMT

Yes. I got correct reults once I aggregated with the dimensions that i have used.

sum(Aggr(if(FILE_STATUS='OPEN',sum( {<[%GL_EFF_DT]={'>=$(vBillStartDt)<=$(vBillEndDt)'}>}ITEM_AMT),

sum({<[%REMOVAL_DT]={'>=$(vBillStartDt)<=$(vBillEndDt)'}>}ITEM_AMT)),FILE_STATUS,Trans_Kind))

Thank you.