topic Re: zero total value while using function in qlikview in QlikView

zero total value while using function in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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 .

Re: zero total value while using function in qlikview

sunny_talwar — Fri, 06 May 2016 12:49:12 GMT

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)

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

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.

Re: zero total value while using function in qlikview

swuehl — Fri, 06 May 2016 12:53:39 GMT

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.

Re: zero total value while using function in qlikview

sunny_talwar — Fri, 06 May 2016 12:58:22 GMT

Here are more simplified version of the expression

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)

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

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

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)

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

Re: zero total value while using function in qlikview

Anonymous — Fri, 06 May 2016 12:58:38 GMT

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

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.