topic Re: aggr(sum(value),dim - positional problem in QlikView

aggr(sum(value),dim - positional problem

Anonymous — Tue, 21 Jul 2015 17:47:59 GMT

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

Re: aggr(sum(value),dim - positional problem

sunny_talwar — Tue, 21 Jul 2015 17:56:50 GMT

Do you want to see this?

Expression:

Aggr(NODISTINCT sum(ClaimAmount),DoctorName)

Re: aggr(sum(value),dim - positional problem

swuehl — Tue, 21 Jul 2015 18:03:14 GMT

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

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

Re: aggr(sum(value),dim - positional problem

Anonymous — Tue, 21 Jul 2015 19:14:16 GMT

Thank you Swuehl. Your suggestion worked perfectly.

David

Re: aggr(sum(value),dim - positional problem

Anonymous — Tue, 21 Jul 2015 19:16:29 GMT

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

Re: aggr(sum(value),dim - positional problem

sunny_talwar — Tue, 21 Jul 2015 19:21:19 GMT

No problem

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

Best,

Sunny

Re: aggr(sum(value),dim - positional problem

Anonymous — Thu, 23 Jul 2015 18:09:28 GMT

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