New to QlikView

Discussion board where members can get started with QlikView.

New Contributor II

@Hi All,

I have tried to create a logic using set analysis which combines both the sum and aggregation function but failed to get the right method. Here's a simple table to illustrate what I wish to achieve:

I would like to count the number of distinct store ids if the total product sale for the whole month for the given store id is less than a threshold value.

In the example below, the threshold value for product sale is 50.

So if you count the number of STORE ID whose given Product Sale is less than 50 for the whole month, then the result is 1 (Store ID A).

Please note that the table is not complete and contains data for only 4 days.

I've tried this function in a text box but I'm getting errors:

=count( aggr(sum({\$<[City] = {'London'}, [Month]={'Jan'}}[Store ID])<50) DISTINCT [Store ID])

Not very sure whether that's the right logic. I would really appreciate your help on this one. Thanks

 Store ID Month Date Product Sale City A Jan 01/01/2018 10 London A Jan 02/01/2018 5 London A Jan 03/01/2018 10 London A Jan 04/01/2018 5 London A Feb 01/02/2018 96 London A Feb 02/02/2018 20 London A Feb 03/02/2018 15 London A Feb 04/02/2018 30 London B Jan 01/01/2018 61 London B Jan 02/01/2018 13 London B Jan 03/01/2018 25 London B Jan 04/01/2018 21 London B Feb 01/02/2018 96 London B Feb 02/02/2018 20 London B Feb 03/02/2018 15 London B Feb 04/02/2018 30 London
Tags (1)
1 Solution

Accepted Solutions
MVP

Count(DISTINCT {<[Store ID] = {"=Sum({<Month = {'Jan'}, City = {'London'}>} [Product Sale]) < 50"}, Month = {'Jan'}, City = {'London'}>}[Store ID])

21 Replies
New Contributor II

MVP

You want this only for Jan?

May be this

Count(DISTINCT Aggr(If(Sum({<Month = {'Jan'}>}[Product Sales]) < 50, [Store ID]), [Store ID], Month))

MVP

Or this for all months

Count(DISTINCT Aggr(If(Sum([Product Sales]) < 50, [Store ID]&Month), [Store ID], Month))

New Contributor II

Hi stalwar1‌ ,

Just to give you some context:

I have created a variable and linked it to a drop down that shows different months. By clicking on each month, you can see the total number of stores (store id) that have sold less than 50 items for the selected month.

So yes, I would like to count the STORE ID for every month.

Many thanks,

RK

New Contributor II

Let me try this and get back to you! Thanks

MVP

So, at any given point of time, you will only be looking at stores for a single month, is that right? Try this

Count(DISTINCT {<[Store ID] = {"=Sum({<Month = {'Jan'}>} [Product Sales]) < 50"}>}[Store ID])

New Contributor II

Yes that's right. I would also like to include the City as well. I want to only filter by London. Where do I place it in the set analysis logic you just showed me? Thanks

MVP

May be this

Count(DISTINCT {<[Store ID] = {"=Sum({<Month = {'Jan'}, City = {'London'}>} [Product Sales]) < 50"}, {<Month = {'Jan'}, City = {'London'}>}[Store ID])

New Contributor II