9 Replies Latest reply: Mar 27, 2014 10:07 AM by Michael Maeuser

# Formula Problem (Set Analysis)

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)

Hi Julio,

you can create a calculated dimension

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

best regards

• ###### Re: Formula Problem (Set Analysis)

Btw, sometimes it is working, sometimes is not.

• ###### Re: Formula Problem (Set Analysis)

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

• ###### Re: Formula Problem (Set Analysis)

Hi Julio,

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

• ###### Re: Formula Problem (Set Analysis)

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)

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

• ###### Re: Formula Problem (Set Analysis)

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!