
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
how to get distinct values of a column for any selection for calculations
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 .

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe try
=Sum( Aggr( Only(Values), Shop_id))
Or change your data model so that the facts are only kept once for shop_id.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 .


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
