topic Set Analysis Doubles the value in QlikView

Set Analysis Doubles the value

deshikas — Wed, 25 Nov 2020 16:16:04 GMT

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 — Mon, 23 May 2016 15:35:44 GMT

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

Gabriel — Mon, 23 May 2016 15:46:24 GMT

Hi,

Maybe if you include single quote around the Current like this

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

Hope this helps

ThornOfCrowns — Mon, 23 May 2016 15:47:01 GMT

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

m_woolf — Mon, 23 May 2016 19:21:59 GMT

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 — Mon, 23 May 2016 19:29:01 GMT

How about this?

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