Set Analysis Doubles the value

[deshikas]

I have a pivot table when I sum the values it doubles the value.

Entity   Status   Client_No    Flag    Bal    Sum(Bal)

01        Current 123                1       75        150

Sum ({<D_Status = {Current},D_Flag ={1}>} Bal)

Sum should be 75.

Tried distinct

Sum ({<D_Status = {Current},D_Flag ={1}>} Distinct Bal) this removes the duplicates but if there are other clients where Bal = 75, they are ignored.

[trdandamudi]

Can you please check you data and reconfirm... If you still  have the issue can you please post a small sample file...

[Gabriel]

Hi,

Maybe if you include single quote around the Current like this

Sum({<Status = {'Current'}>}Bal)

Hope this helps

[ThornOfCrowns]

You'll need to show the underlying data of the pivot to give us a chance to see the problem, I think.

[m_woolf]

I'm betting that you have duplicate records and that if you do:

Count({<D_Status = {Current},D_Flag ={1},Entity={'01'}>} Entity)

you'll get 2.

[sunny_talwar]

How about this?

Sum(Aggr(Only({<D_Status = {Current}, D_Flag ={1}>} Bal), Client_No))