topic Re: Formula Problem (Set Analysis) in QlikView

Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:17:57 GMT

Guys, good morning,

I have an Excel file extraction from a financial parts that keeps repeating the same information in many lines in the first columns, and then the last columns it changes. I want to get the total for those first columns, so, I wrote this formula in my script:

IF([Claim #]=PREVIOUS([Claim #]),'0','1') as UNIQUE_FLAG

after that, I created this other formula to sum the total approved from the Claims:

=SUM({<[UNIQUE_FLAG]={1}, [Status Claim] -={Cancelled}>}([Total Approved])*[Rate])

Problem is, it seems it is getting always the double (as if it is sum when UNIQUE_FLAG = 1 and UNIQUE_FLAG = 0).

When I choose for one single Claim, it becomes very clear that it's doubling the value.

Can you guys help me out in this problem?

I'll attach a pic of it...

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:24:34 GMT

Hi Julio,

you can create a calculated dimension

Aggr (Only ({<UNIQUE_FLAG = {1} Claim#), Claim#)

best regards

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:38:47 GMT

Where should I add that?

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:44:08 GMT

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:44:20 GMT

Btw, sometimes it is working, sometimes is not.

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 13:48:57 GMT

Nope, like that didn't work... the problem is in the sum formula...

Re: Formula Problem (Set Analysis)

jvishnuram — Thu, 27 Mar 2014 13:53:53 GMT

Hi Julio,

Could you please share your application, so that the people can play around with the formula.

Re: Formula Problem (Set Analysis)

Thu, 27 Mar 2014 14:00:58 GMT

Unfortunately I can't , classified information...

I just really don't get why it works for some Claims, and for another doesn't work.

Re: Formula Problem (Set Analysis)

jerem1234 — Thu, 27 Mar 2014 14:05:38 GMT

does

SUM({<[UNIQUE_FLAG]={0}, [Status Claim] -={Cancelled}>}([Total Approved])*[Rate])

behave in a similar fashion?

Try adding unique flag as a dimension in your chart and see if it splits out the double sum. If it doesn't, then the flag should be working correctly.

Maybe try using:

count(Claim#) as expression or count([UNIQUE_FLAG]) to see if its counting more than 1 record.

Hope this helps!

Re: Formula Problem (Set Analysis)

michael_maeuser — Thu, 27 Mar 2014 14:07:18 GMT

shouldn´t {Cancelled} be in {'Cancelled'}?