QlikView App Dev

[#1]

I have table like below:		(qvd)
INVYEAR	INVMONTH	INV_NO	INV_CODE	INV_LC_AMT	MATCH_LC_AMT
2016	Dec	135164237	CINV	427936.95	80000
2016	Dec	135164237	CINV	427936.95	327064.27
2016	Dec	135164237	CINV	427936.95	20872.68
2016	Feb	135164012	CINV	5540	1000
2016	Feb	135164012	CINV	5540	2000
I need report as below:
INVYEAR	INVMONTH	INV_NO	INV_CODE	INV_LC_AMT	MATCH_LC_AMT
2016	Dec	135164237	CINV	427936.95	427936.95
2016	Feb	135164012	CINV	5540	3000

Please advise.

Thanks

[prieper]

Create a straight-Table with

INVYEAR

INVMONTH

INV_NO

INV_CODE

as dimensions

and expressions:

SUM(DISTINCT INV_LC_AMT), or AVG(INV_LC_AMT)

SUM(MATCH_LC_AMT)

Peter

[sibusiso90]

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 · Accepted solution]

Below is the qvw where this is done

[kamal_sanguri]

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]

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

[#8]

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))

[#9]

NVYEAR	INVMONTH	INV_NO	INV_CODE	INV_LC_AMT	MATCH_LC_AMT
2016	Dec	135164237	CINV	427936.95	80000
2016	Dec	135164237	CINV	427936.95	327064.27
2016	Dec	135164237	CINV	427936.95	20872.68
2016	Feb	135164012	CINV	5540

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.