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: 
Not applicable

Chart based first row and sum of multiple releated row

I have table like below:(qvd)
INVYEARINVMONTHINV_NOINV_CODEINV_LC_AMTMATCH_LC_AMT
2016Dec135164237CINV427936.9580000
2016Dec135164237CINV427936.95327064.27
2016Dec135164237CINV427936.9520872.68
2016Feb135164012CINV55401000
2016Feb135164012CINV55402000
I need report as below:
INVYEARINVMONTHINV_NOINV_CODEINV_LC_AMTMATCH_LC_AMT
2016Dec135164237CINV427936.95427936.95
2016Feb135164012CINV55403000

Please advise.

Thanks

1 Solution

Accepted Solutions
sibusiso90
Creator III
Creator III

Below is the qvw where this is done

View solution in original post

8 Replies
prieper
Master II
Master II

Create a straight-Table with

INVYEARINVMONTHINV_NOINV_CODE

as dimensions

and expressions:

SUM(DISTINCT INV_LC_AMT), or AVG(INV_LC_AMT)

SUM(MATCH_LC_AMT)

Peter

sibusiso90
Creator III
Creator III

You will have

Invyear invmonth inv_no inv_code as dimentions

inv_lc_amt  match_lc_amtbe your expressions

where

only(inv_lc_amt) will be your first expression

and

sum(match_lc_amtbe) will be your second expression

sibusiso90
Creator III
Creator III

Below is the qvw where this is done

kamal_sanguri
Specialist
Specialist

Pls refer this..

Kushal_Chawda

Try below expressions

For INV_LC_AMT


Sum(DISTINCT INV_LC_AMT)


For MATCH_LC_AMT

Sum(MATCH_LC_AMT)

sibusiso90
Creator III
Creator III

Hi Mohamed

Please when you have found your solution please mark as correct and the ones which were helpful please mark as helpful so that you can also help others with your question and so that other people don't respond endlessly while you have the response

Not applicable
Author

Dear Sibusiso,

Please suggest me for following dimension only

invyear,invmonth for the same expression.

It is working if I have invno, invcode; But please suggest only for INVYEAR,INVMONTH

My expression:

sum(IF(DRCR_FLAG='D',MATCH_LC_AMT,-MATCH_LC_AMT))
ONLY(IF(DRCR_FLAG='C',INV_LC_AMT,-INV_LC_AMT))
Not applicable
Author

NVYEARINVMONTHINV_NOINV_CODEINV_LC_AMTMATCH_LC_AMT
2016Dec135164237CINV427936.9580000
2016Dec135164237CINV427936.95327064.27
2016Dec135164237CINV427936.9520872.68
2016Feb135164012CINV5540

Sorry not to mention before, I checked out when the result total is wrong. There are invoices where there is no matching MATCH_LC_AMT, in my context not yet collected.

Please advise. Thanks for all help.