zero total value while using function in qlikview

[#1]

Dear Experts ,

I am designing a simple report where i used Fabs function in expression . I am not getting total in bottom of chart .

Could you please check my file and suggest .

[sunny_talwar · Accepted solution]

Might be able to simplfy this, but this seems to be working:

Sum(Aggr(if ((GL_HEAD = 2 and (sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) - sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))) > 0)

or

(GL_HEAD=1 and (sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) - sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))) > 0 and IS_BANK_ACCOUNT = 'Y')

or

(GL_HEAD=1 and (sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) - sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))) < 0 and IS_BANK_ACCOUNT = 'N'),

fabs((sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) - sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)))),0), Group, Subgroup))

UPDATE:

Need to use Aggr() function to get the total right and when you use Aggr() you cannot use Column() function, unfortunately.

[swuehl]

I don't think it's about the fabs() function, it's about that in your expression

if ((GL_HEAD = 2 and Column(3) > 0) or (GL_HEAD=1 and Column(3) > 0 and IS_BANK_ACCOUNT = 'Y') or (GL_HEAD=1 and Column(3) < 0 and IS_BANK_ACCOUNT = 'N') ,fabs(Column(3)),0)

Field values GL_HEAD and IS_BANK_ACCOUNT are not defined to a unique value in the context of your total line (read also:

Use Aggregation Functions!

You could use advanced aggregation, like

Sum( Aggr(

if ((GL_HEAD = 2 and Column(3) > 0) or (GL_HEAD=1 and Column(3) > 0 and IS_BANK_ACCOUNT = 'Y') or (GL_HEAD=1 and Column(3) < 0 and IS_BANK_ACCOUNT = 'N') ,fabs(Column(3)),0)

,Group, SubGroup))

But you would need to replace the column(3) references with the full expressions.

[sunny_talwar]

Here are more simplified version of the expression

1)

Sum(Aggr(

if(

(GL_HEAD = 2 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) > 0)

or

(GL_HEAD = 1 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) > 0 and IS_BANK_ACCOUNT = 'Y')

or

(GL_HEAD = 1 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) < 0 and IS_BANK_ACCOUNT = 'N'),

fabs((fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)))),0), Group, Subgroup))

2)

Sum(Aggr(

if(

(GL_HEAD = 2 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) < 0)

or

(GL_HEAD = 1 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) < 0 and IS_BANK_ACCOUNT = 'Y')

or

(GL_HEAD = 1 and (fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT))) > 0 and IS_BANK_ACCOUNT = 'N'),

fabs((fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)) - fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)))),0), Group, Subgroup))

[Anonymous]

On totals you can't use that (bold marked):

if ((GL_HEAD=1 and Column(3) > 0 and IS_BANK_ACCOUNT = 'N') OR (GL_HEAD=1 and Column(3) < 0 and IS_BANK_ACCOUNT = 'Y')

Because dimension values have a lot of data, and IF is not an accumulative expression.

Change Column(3) for the formulas, and aggregate it:

For inflow expression:

sum(aggr(

if (

    (GL_HEAD = 2 and sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))-sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) > 0)

    or (GL_HEAD=1 and sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))-sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) > 0 and IS_BANK_ACCOUNT = 'Y')

    or (GL_HEAD=1 and sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))-sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)) < 0 and IS_BANK_ACCOUNT = 'N'),

    fabs(sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMinDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup))-sum(aggr(fabs(sum({<DOCUMENT_DATE={"<=$(vMaxDate)"},GL_HEAD={'1','2'}>}LOCAL_AMOUNT)),Group,Subgroup)))

,0),Group,Subgroup))

Regards.