aggr(sum(value),dim - positional problem

[Anonymous]

I have the following sample inline script:

Load *Inline [

DoctorName,ClaimBatchNo,ClaimNumber,ClaimAmount

DrPenfield,120,01,725.75

DrJones,124,01,500.00

DrJones,124,01,-500.00

DrJones,123,01,650.95

DrJones,123,01,300.00

DrJones,123,02,87.50

DrJones,125,01,250.75

DrGraham,126,01,450.75

DrGraham,126,02,250.75

DrGraham,126,03,100.25

DrGraham,126,04,350.00

];

A doctor may have one or more Claim Batches[ClaimBatchNo] and one or more Claims[ClaimNumbers] within each ClaimBatchNo.  A ClaimAmount can be positive or negative.  Using the expression aggr(sum(ClaimAmount),DoctorName), I get the correct values for outstanding claims by Doctor but when an individual ClaimNumber as in the case of ClaimNumber 124 for Dr. Jones below is equal to 0, the outstanding claim amount shows up on the incorrect line.  I want the outstanding amount to be on the same line as the Doctor's name as indicated in the red/circle/arrow below.  

The attached ZeroSumProblem.qvw gives me the following output:

I only have the Personal Edition of Qlikview at this time so if anyone can help me, I cannot read a response if it is contained in a .QVW returned file.

Thanks

Dave

[sunny_talwar]

Do you want to see this?

Expression:

Aggr(NODISTINCT sum(ClaimAmount),DoctorName)

[swuehl]

You can use a quite similar expression as used for your dimension formatting:

= if(above(total DoctorName)=DoctorName,' ',sum(Total<DoctorName> ClaimAmount))

[Anonymous]

Thank you Swuehl.  Your suggestion worked perfectly.

David

[Anonymous]

Hello Sunny:

I did try your suggested approach a couple of days ago.  As you can see, it gives the total amount per doctor on every line.  This is not what I wanted as the total must only appear on the Doctor first line.

Thanks

DAvid

[sunny_talwar]

No problem

I am glad swuehl‌ was able to help you out.

Best,

Sunny

[Anonymous]

Hi Swuehl:

Your suggestions works but if you would not mind, can you explain what the purpose is for the ' ' in the expression that you gave me:  if(above(total DoctorName)=DoctorName,' ',sum(Total<DoctorName> ClaimAmount)).

Thanks

Dave