Expression giving incorrect amounts

[manjunaths]

Hi All,

I am developing an application with following data set.

Payer	ClaimID	Bill No	f_finalized_date	f_transaction_date	transaction_type	trans_amount	detail_id	sub_sequence
A	C1318000007189	BL131801007288	08-01-2018	10-01-2018	S	419.1	78343568	1
A	C1318000007189	BL131801007288	08-01-2018	21-06-2018	S	159.3	82358396	3
A	C1318000007189	BL131801007288	08-01-2018	16-09-2018	S	159.3	82650530	4
A	C1318000007189	BL131801007288	08-01-2018	01-04-2018	S	389.3	81650311	2
A	C1318000007189	BN131801000055	08-01-2018	10-01-2018	S	86.8	78783468	1
A	C1318000007189	BL131801007288	08-01-2018	17-05-2018	R	230	82072370	2
A	C1318000007189	BL131801007288	08-01-2018	07-03-2018	R	116.6	79977825	1
A	C1318000007189	BL131801007288	08-01-2018	27-09-2018	R	0	82732216	4
A	C1318000007189	BL131801007288	08-01-2018	01-08-2018	R	0	82535459	3
A	C0218000089611	BL021808007269	03-09-2018	11-09-2018	S	283.06	76116559	1
A	C0218000089611	BL021808007269	03-09-2018	24-10-2018	R	257.06	78787889	1
B	C0318000306897	BL031809017870	20-09-2018	22-09-2018	S	170.34	76475823	1
B	C0318000306897	BL031809017870	20-09-2018	11-10-2018	R	0	79428571	1
C	C0917000073683	BL091712006197	29-12-2017	04-01-2018	S	276.6	77647803	1
C	C0917000073683	BL091712006197	29-12-2017	27-07-2018	S	276.6	82302975	3
C	C0917000073683	BL091712006197	29-12-2017	05-04-2018	S	276.6	81633744	2
C	C0917000073683	BL091712006197	29-12-2017	09-11-2018	R	0	82483792	3
C	C0917000073683	BL091712006197	29-12-2017	16-03-2018	R	0	79819656	1
C	C0917000073683	BL091712006197	29-12-2017	02-07-2018	R	0	82056646	2

With above data I want gain the following output.

claim_id	bill_no	Resub Pending < 45 days	Resub Pending > 45 days
C0218000089611	BL021808007269	26	0
C0318000306897	BL031809017870	170.34	0
C1318000007189	BL131801007288	0	159.3
BN131801000055	0	0
C0917000073683	BL091712006197	276.6	0

But I am getting the output as follows which is wrong as per requirement.

But when I select only a single ClaimID, then I am getting following output which is correct.

I am using expressions as following.

1. For Resub pending < 45 days

sum(AGGR(if($(vLatestRA)>$(vLatestSubmission) AND Today()-$(vLatestRA))>=0 AND (Today()-$(vLatestRA))<=45,
sum({<[closure_type]-={'D','F'}, sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'S'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount)-
sum({<[closure_type]-={'D','F'}, sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'R'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount),0),payer,claim_id,bill_no))

2. For Resub pending > 45 days

sum(AGGR(if($(vLatestRA)>$(vLatestSubmission) AND (Today()-$(vLatestRA))>45 ,
sum({< sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'S'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount)-
sum({<sub_sequence={'$(=$(vSeq))'},
[transaction_type]={'R'},[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"}>}
trans_amount),0),payer,claim_id,bill_no))

Please can anyone let me know what is the wrong in my current expressions. Or suggest a better way to get desired output.

Thanks.

[johnca]

It isn't possible with the information provided in as much as you don't specify the definitions for the variables. It appears you are looking for the difference between trans_amount for the last two entries by ClaimID and [Bill No] but that is only a guess.

Can you attach your qvw?

[manjunaths]

Hi Johncaqc,

Thanks for your response.
As you expect , definition for the variables are as follows.
vLatestRA = max({<[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"},[transaction_type]={'R'} ,[trans_amount]={">=0"} >} f_transaction_date)

vLatestSubmission = max({<[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"},[transaction_type]={'S'} >} f_transaction_date)

vSeq = max({<[f_finalized_date]={">=$(=$(vNumSF)) <=$(=$(vNumST))"},[transaction_type]={'S'} >} sub_sequence)

I am looking for the difference between latest trans_amount of trans_type='S' and latest trans_amount of trans_type='R' .

[johnca]

There seems to be some inconsistencies in your data. Your desired output shows value BN131801000055 in the claim_id filed but is not. It is a bill_id. But, regardless...

I also don't see where you are getting a +/-45 day variable to determine Resub Pending < 45 Days or > 45 Days. For the above mentioned bill_id and result of 159.3 (difference between the latest values for S & R) the difference is only 11 days. And since all the dates occur in January 2018 I cannot use today() to compare to.  I would create +/- 45 day flags based on whatever you're using to determine them, in the script. 

Several variables you did not define; vNumSF, vNumST.

Much of what you want to do should be done in the script;

First, try using LastValue() and group by Payer, ClaimID, [Bill No] and transaction_type. This will give you the last values for each desired pair. You shouldbe able to figure the rest out from that.

Without better definitions that's about as far as I can help you.

V/r, John