Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
JASalinas
Contributor III
Contributor III

Get Positive Value in KPI Accounting

Hello my friends I need your help

I have the following table with its expressions:

ACCNT_CODE = Accounts

Debit = Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT])

Credit = Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT])

Balance = Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT])

As show

JASalinas_0-1606320209119.png

 I want to do the sum the values that are positive in the Balance, is there a way to do this?

Thanks in advance

Labels (4)
1 Solution

Accepted Solutions
Kush
MVP
MVP

@JASalinas  try below

=sum(aggr(if((Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]))>0,

Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]),0), Account))

View solution in original post

8 Replies
Kush
MVP
MVP

@JASalinas  try below

Sum({<[AMOUNT]={">0"},[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[AMOUNT]={">0"},[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT])

JASalinas
Contributor III
Contributor III
Author

Hi @Kush thank for you answer, I tried but in the balance column it show me the same as the credit column. 

Kush
MVP
MVP

@JASalinas  try below

if((Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]))>0,

Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]),0)

JASalinas
Contributor III
Contributor III
Author

Hi @Kush Well we're moving on, show me the following

JASalinas_0-1606323398914.png

The sum is equal to zero, it is not doing the sum...

Kush
MVP
MVP

@JASalinas  you can set the total function option to "sum" in measure properties

Kush_0-1606323737912.png

 

JASalinas
Contributor III
Contributor III
Author

@Kush Yes, it already does the sum, but how can I do this in KPI? The KPI does not have the sum option.

Kush
MVP
MVP

@JASalinas  try below

=sum(aggr(if((Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]))>0,

Sum({<[D_C]={"D"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]) + Sum({<[D_C]={"C"},[ACCNT_CODE]={"12*", "13*", "14*","32*", "33*", "34*","A*", "E*", "R*"}>}[AMOUNT]),0), Account))

View solution in original post

JASalinas
Contributor III
Contributor III
Author

@Kush Uff, Excelent My Friend, Thank you very very much... It's work!....