topic Re: Tricky Gross amount calculation in QlikView

Tricky Gross amount calculation

AH — Wed, 07 Jan 2015 15:54:35 GMT

Hi,

I have a scenario like this, I have to calculate the Gross Amount summing up the SalesRegister, Service and FreeText amount. The problem here is, I have to exclude couple of GL account from FreeText while summing up. And this GL account shouldnt be excluded from SalesRegister and Service while summing up.

My Expression is like this:
=num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}TotalLineAmountInReportCurrency),'##,##0')

Accounts to be excluded from Freetext only is: 100, 101, 115, 120, 430 and 420

Its sounds little tricky to me to calculate this.

Any Suggestions? Please

Thanks,
Shan

Re: Tricky Gross amount calculation

anbu1984 — Wed, 07 Jan 2015 16:02:48 GMT

=num(Sum({<SalesType = {'SalesRegister', 'Service'}>}TotalLineAmountInReportCurrency) +

Sum({<SalesType = { 'FreeText'},GlAcct-={100, 101, 115, 120, 430, 420}>}TotalLineAmountInReportCurrency)

,'##,##0')

Re: Tricky Gross amount calculation

agomes1971 — Wed, 07 Jan 2015 16:07:41 GMT

Hi,

=num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}TotalLineAmountInReportCurrency),'##,##0')-

num(Sum({$<Accounts={100,101,115,120,'430 and 420'},SalesType={'FreeText'}>}TotalLineAmountInReportCurrency),'##,##0')

maybe?

Regards

André Gomes

Re: Tricky Gross amount calculation

AH — Wed, 07 Jan 2015 16:20:23 GMT

Hi Anbu,

The expression looks ok but its showing red marks and the field TotalLineAmountInReportCurrency is not showing right. Could you please have a look at the attachment?

Thanks,

Shan

Re: Tricky Gross amount calculation

agomes1971 — Wed, 07 Jan 2015 16:24:27 GMT

Hi,

the GLAccount is a string ?

Regards

André Gomes

Re: Tricky Gross amount calculation

AH — Wed, 07 Jan 2015 16:29:12 GMT

Yes thats a String.

Thanks,

Shan

Re: Tricky Gross amount calculation

agomes1971 — Wed, 07 Jan 2015 16:30:30 GMT

Glad to help.

André Gomes

Re: Tricky Gross amount calculation

AH — Wed, 07 Jan 2015 16:36:57 GMT

Hi Andre,

Thanks for your reply. Can you explain Why this two giving slightly different numbers?

{$<Accounts={100,101,115,120,'430 and 420'}

{$<Accounts={100,101,115,120,430,420}

Thanks,

Shan

Re: Tricky Gross amount calculation

agomes1971 — Wed, 07 Jan 2015 17:22:10 GMT

Yes because these are different type of selections... but you should use {$<Accounts={100,101,115,120,430,420}. The , it's like an OR...

Regards

André Gomes

Re: Tricky Gross amount calculation

AH — Wed, 07 Jan 2015 17:32:46 GMT

So In my case you suggest this one

{$<Accounts={100,101,115,120,430,420}

Than this one?

{$<Accounts={100,101,115,120,'430 and 420'}

Thanks,

Shan

Re: Tricky Gross amount calculation

agomes1971 — Wed, 07 Jan 2015 18:23:42 GMT

Yes but you should test also with an excel to see which is the real result...

André Gomes

Re: Tricky Gross amount calculation

anbu1984 — Thu, 08 Jan 2015 02:52:54 GMT

Red marks is a bug in Qlikview

=num(Sum({<SalesType = {'SalesRegister', 'Service'}>}TotalLineAmountInReportCurrency) +

Sum({<SalesType = { 'FreeText'},GlAcct-={'100', '101', '115', '120', '430', '420'}>}TotalLineAmountInReportCurrency)

,'##,##0')