topic Re: Subtotal - IF Function - SUM - AR Aging in QlikView

Subtotal - IF Function - SUM - AR Aging

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi I have problem on expression syntax. New on using QBAR-QlikView.

if((Age >= 0) and (Age<= 30),OpenBalance*1,0)

Can somebody help me with the code expression.

I think I miss SUM but i dont know the code.

Thanks in advance

Re: Subtotal - IF Function - SUM - AR Aging

Anil_Babu_Samineni — Sun, 09 Jul 2017 16:19:13 GMT

May be this?

if((Age >= 0 and Age<= 30,OpenBalance*1,0)

Sum(Aggr(if((Age >= 0 and Age<= 30,OpenBalance*1,0), Type))

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Mon, 10 Jul 2017 00:39:38 GMT

Hi Anil Thank you for your reply.

I dont know what's wrong on this sum. here is the snap

Re: Subtotal - IF Function - SUM - AR Aging

Kushal_Chawda — Mon, 10 Jul 2017 07:11:54 GMT

You need to include this condition inside aggregation function so that to aggregate your data with respect to your dimension

=sum(if((Age >= 0) and (Age<= 30),OpenBalance*1,0))

Re: Subtotal - IF Function - SUM - AR Aging

Kushal_Chawda — Mon, 10 Jul 2017 07:31:57 GMT

It's always better to write set analysis

=sum({<Age={">=0<=30"}>}OpenBalance*1)

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Mon, 10 Jul 2017 07:40:53 GMT

Re: Subtotal - IF Function - SUM - AR Aging

Anil_Babu_Samineni — Mon, 10 Jul 2017 07:43:19 GMT

Try this?

Sum(Aggr(If(Age >= 0 and Age <= 30, OpenBalance * 1, 0), [Customer.Name]))

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Mon, 10 Jul 2017 07:44:53 GMT

Hi Kushal Bad field names:

Age & OpenBalance is a Expression Column

Re: Subtotal - IF Function - SUM - AR Aging

mangalsk — Mon, 10 Jul 2017 07:44:59 GMT

try this

sum(aggr(sum({<Age={">=0<=30"}>}OpenBalance)*1+sum({<Age-={">=0<=30"}>}OpenBalance)*0,[Customer.Name]))

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Mon, 10 Jul 2017 07:53:46 GMT

Hi Sir Anil, The expression is OK. but the result comes "0".

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Mon, 10 Jul 2017 07:58:05 GMT

Hi Sire Mangal,

Result is "0"

Re: Subtotal - IF Function - SUM - AR Aging

Anil_Babu_Samineni — Tue, 11 Jul 2017 02:31:12 GMT

Can you post the application?

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Tue, 11 Jul 2017 06:29:49 GMT

Hi Anil,

I am using QBAR

Quickbooks Advanced Reporting

The function is Qlik-View.

Re: Subtotal - IF Function - SUM - AR Aging

Anil_Babu_Samineni — Tue, 11 Jul 2017 06:35:54 GMT

Even though, You are building thru Qlikview only, Right? So then please create sample qvw and share with us that may useful to you

Re: Subtotal - IF Function - SUM - AR Aging

Kushal_Chawda — Tue, 11 Jul 2017 06:55:37 GMT

what is expression for age and openbalance?

Re: Subtotal - IF Function - SUM - AR Aging

mangalsk — Tue, 11 Jul 2017 10:43:36 GMT

Dear ,

Both of following expression working for me please check also make one setting in Presentation tab -->uncheck Suppress Zero-Values. If it doesn't work plz send dummy data.

sum(aggr(sum({<age={">=0<=30"}>}OpenBalance)*1+

sum({<age-={">=0<=30"}>}OpenBalance)*0,[CustomerName],age))

sum(aggr(if(age>=0 and age<=30 ,sum(OpenBalance)*1,sum(OpenBalance)*0),[CustomerName],age))

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Tue, 11 Jul 2017 11:30:09 GMT

Hi Mangal Here is the file

Dropbox - AR Aging Detail - V300.zip

Thank you

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Tue, 11 Jul 2017 11:32:58 GMT

Hi Anil,

Here is the file report.

Dropbox - AR Aging Detail - V300.zip

Thanks.

Re: Subtotal - IF Function - SUM - AR Aging

mangalsk — Tue, 11 Jul 2017 12:31:02 GMT

have you tried setting it should work.

Provide attach file sample data

Re: Subtotal - IF Function - SUM - AR Aging

Anonymous — Wed, 19 Jul 2017 14:56:04 GMT

My problem is Im not getting sum.

this formula works but no sum.

if((Age >= 0) and (Age <= 30),OpenBalance*1,0)

if((Age >= 31) and (Age <= 60),OpenBalance*1,0)

if((Age >= 61) and (Age <= 90),OpenBalance*1,0)

if((Age >= 121),OpenBalance*1,0)

I name columns / expression as

Age:

_ReportFilterAsOfDate - Date

OpenBalance:

sum(If (Transactions.Pending = 0 or IsNull(Transactions.Pending),

if ((Match ([Transactions.Txn Type], 'Receive Payment', 'Credit Memo', 'CreditCardCredit') or (Match ([Transactions.Txn Type], 'Journal') and Match([Transactions.Debit Or Credit], 'Credit'))), -[Transactions.Balance Remaining], [Transactions.Balance Remaining])))