how to get distinct values of a column for any selection for calculations

[singbittoo]

Zone	Country	Initiatives	Shop_id	Values
North America	USA	hologram	111	100
North America	USA	freezer	222	200
North America	USA	freezer	111	100
North America	Canada	hologram	333	300
North America	Canada	freezer	333	300
North America	Mexico	sticker	444	400
Europe	France	hologram	555	500
Europe	France	freezer	666	600
Europe	France	sticker	555	500
Europe	France	lights	666	600
Europe	UK	coolers	777	700

the above is a dummy data . Now if no filter is selected all the 11 rows will come and if we use sum(Values) in our expression the we will get = 4300 but what i need is where ever there is distinct Shop_id as if the shop_id is repeted twice we should use the value once for our calculation so the sum should be = 2800 as Shop_id 111 , 333 , 444 , 666 is repeated so we have to consider the values for this only once

also for one shop_id the value will always be same and one shop_id can have multiple initiatives ..

Please let me know how to achieve this at expression level in qlik .

[sunny_talwar]

May be this:

Sum(Aggr(Only(Values), Shop_id))

or

Sum(Aggr(Avg(Values), Shop_id))

or

Sum(Aggr(Sum(DISTINCT Values), Shop_id))

or

Sum(Aggr(Max(Values), Shop_id))

or

Sum(Aggr(Min(Values), Shop_id))

all of these should work

[swuehl]

Maybe try

=Sum( Aggr( Only(Values), Shop_id))

Or change your data model so that the facts are only kept once for shop_id.

[singbittoo]

thanks for you reply but we cant keep only one shop_id as user can choose initiative from the filter and one shop_id can be across multiple initiatives so we cant remove any rows from the data model .

[swuehl]

Not sure I understand, I just suggested that you potentially could remodel your data model, so that the facts (Values field) are stored in a way without duplicates (as it appear to be the case right now).

In you case, it might be better to remove the initiaives from the fact table, store them in a separate table and link initiatives via shop_id to the facts.

But it's hard to tell a better solution without knowing your full data and requirements. Just note that using something like advanced aggregation (aggr() function, just to remove the duplicate Values) introduces an overhead in the front end calculations that could be removed by changing the model.