Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

manjunaths
New Contributor

Expression giving incorrect amounts

Hi All,

I am developing an application with following data set.

PayerClaimIDBill Nof_finalized_datef_transaction_datetransaction_typetrans_amountdetail_idsub_sequence
AC1318000007189BL13180100728808-01-201810-01-2018S419.1783435681
AC1318000007189BL13180100728808-01-201821-06-2018S159.3823583963
AC1318000007189BL13180100728808-01-201816-09-2018S159.3826505304
AC1318000007189BL13180100728808-01-201801-04-2018S389.3816503112
AC1318000007189BN13180100005508-01-201810-01-2018S86.8787834681
AC1318000007189BL13180100728808-01-201817-05-2018R230820723702
AC1318000007189BL13180100728808-01-201807-03-2018R116.6799778251
AC1318000007189BL13180100728808-01-201827-09-2018R0827322164
AC1318000007189BL13180100728808-01-201801-08-2018R0825354593
AC0218000089611BL02180800726903-09-201811-09-2018S283.06761165591
AC0218000089611BL02180800726903-09-201824-10-2018R257.06787878891
BC0318000306897BL03180901787020-09-201822-09-2018S170.34764758231
BC0318000306897BL03180901787020-09-201811-10-2018R0794285711
CC0917000073683BL09171200619729-12-201704-01-2018S276.6776478031
CC0917000073683BL09171200619729-12-201727-07-2018S276.6823029753
CC0917000073683BL09171200619729-12-201705-04-2018S276.6816337442
CC0917000073683BL09171200619729-12-201709-11-2018R0824837923
CC0917000073683BL09171200619729-12-201716-03-2018R0798196561
CC0917000073683BL09171200619729-12-201702-07-2018R0820566462

 

With above data I want gain the following output.

claim_idbill_noResub Pending < 45 daysResub Pending > 45 days
C0218000089611BL021808007269260
C0318000306897BL031809017870170.340
C1318000007189BL1318010072880159.3
BN13180100005500
C0917000073683BL091712006197276.60

 

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

1.jpg

 

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

2.jpg

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.

3 Replies
johncaqc
Valued Contributor

Re: Expression giving incorrect amounts

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
New Contributor

Re: Expression giving incorrect amounts

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' .
johncaqc
Valued Contributor

Re: Expression giving incorrect amounts

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