Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Difference between two Sums in related tables where only exists in one

I've got two related tables; ACCOUNTDATA and FINANCIALDATA.

I've got a chart that has dimensions from ACCOUNTDATA .

I've got a sum of ACCOUNTDATA: sum(ACC_AMT_TAX_REP_A_src_2)

I've got a sum of FINANCIALDATA:  sum(AMT_TAX_TAXREP_A_src_1) - BUT the chart is set to suppress Nulls so this sum is only the sum where there's a matching record in ACCOUNTDATA (which is exactly what I want).

NN Acc Rec.PNG

The challenge is create a Formula ( that I can put in a variable / Text Object ) with the Difference value.

i.e. sum(ACC_AMT_TAX_REP_A_src_2) - sum(AMT_TAX_TAXREP_A_src_1)

BUT only where the AMT_TAX_TAXREP_A_src_1 has a matching record in ACCOUNTDATA. Or in the chart where ACC_GL_ACCOUNT_src_2 is not null.

Any ideas?

 

Labels (1)
1 Solution

Accepted Solutions
gp_oconnor
Partner - Contributor III
Partner - Contributor III

=Num(sum(ACC_AMT_TAX_REP_A_src_2) - sum({<[ACC_GL_ACCOUNT_src_2]*={*}>} AMT_TAX_TAXREP_A_src_1),'#,##0') This gives the desired result when using the QVW you attached.

View solution in original post

3 Replies
Anil_Babu_Samineni

Perhaps this

sum({<Account={"=Sum(AMT_TAX_TAXREP_A_src_1)=Sum(ACC_AMT_TAX_REP_A_src_2)"}>} ACC_AMT_TAX_REP_A_src_2)
Or
Sum({<ACC_AMT_TAX_REP_A_src_2 = {"=Not IsNull()"}>} ACC_AMT_TAX_REP_A_src_2)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shane_spencer
Specialist
Specialist
Author

Not sure what you are getting at with the first suggestion, and the second returns 0. Attached above is the qvw if that helps.

gp_oconnor
Partner - Contributor III
Partner - Contributor III

=Num(sum(ACC_AMT_TAX_REP_A_src_2) - sum({<[ACC_GL_ACCOUNT_src_2]*={*}>} AMT_TAX_TAXREP_A_src_1),'#,##0') This gives the desired result when using the QVW you attached.