Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JASalinas
Creator
Creator

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
Kushal_Chawda

@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
Kushal_Chawda

@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
Creator
Creator
Author

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

Kushal_Chawda

@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
Creator
Creator
Author

Hi @Kushal_Chawda 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...

Kushal_Chawda

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

Kush_0-1606323737912.png

 

JASalinas
Creator
Creator
Author

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

Kushal_Chawda

@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))
JASalinas
Creator
Creator
Author

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