Formula Problem (Set Analysis)

[#1]

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...

[#2]

Hi Julio,

you can create a calculated dimension

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

best regards

[#3]

Where should I add that?

[#4]

[#5]

Btw, sometimes it is working, sometimes is not.

[#6]

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

[jvishnuram]

Hi Julio,

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

[#8]

Unfortunately I can't , classified information...

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

[jerem1234]

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!

[michael_maeuser]

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