topic PLEASE HELP: sum, aggregation and set analysis in QlikView

PLEASE HELP: sum, aggregation and set analysis

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

@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

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 12:17:43 GMT

stalwar1‌ Could you please help here? Many thanks!

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 12:22:13 GMT

You want this only for Jan?

May be this

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

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 12:22:34 GMT

Or this for all months

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

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 12:26:30 GMT

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,

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 12:27:12 GMT

Let me try this and get back to you! Thanks

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 12:28:46 GMT

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])

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 12:33:13 GMT

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

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 12:36:05 GMT

May be this

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

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 12:52:15 GMT

Just wondering - would you not use an aggregation function to sum up the product sale values for each store ID and then see whether the value is < 50 to be able to count that specific store ID. It's almost like creating a separate table and getting the count:

Store ID	Month	Product Sale	City
A	Jan	30	London
A	Feb	161	London
B	Jan	120	London
B	Feb	161	London

Does the logic you mentioned actually sum up the product sale at an individual store id level? Apologies if I'm asking silly questions - would like to understand your logic in depth.

Thanks,

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 12:59:26 GMT

It does sum up at the store level, but assuming that there is only one city and one month to look at

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 13:10:35 GMT

Hi Sunny - I have just tried the logic and obtain an error in expression. I think it is not recognising the SUM function because of the "= before it. ie The font colour of the sum function is not blue. Is there a way to fix this? Thanks.

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 13:15:55 GMT

Had typos... try this

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

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 13:36:22 GMT

Works perfectly! Thanks Sunny!

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 13:36:48 GMT

Super

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 14:05:28 GMT

Hi Sunny,

Sorry to bother you again. I have forgotten to add a second condition in that logic. I need to count the number of distinct Store ID if:

(1) the store has sold less than 50 items (Product Sale < 50 ) - Already covered

(2) and has been open for more than 3 days.

I was thinking of adding another condition in the one you posted. Something like max(Date) - min(Date) > 3 for each Store ID. Is it possible to embed such a logic within the one you showed me. If so, where would it be placed?

Many thanks,

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 14:16:34 GMT

May be this

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

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 14:21:23 GMT

Will it take account the other months? In this case the min date would be 01/01/2018 and max date would be 04/02/2018. Because you set the month to be Jan in the set analysis, will it take the other months into consideration? Many thanks

Re: PLEASE HELP: sum, aggregation and set analysis

sunny_talwar — Mon, 16 Apr 2018 14:22:04 GMT

It should

Re: PLEASE HELP: sum, aggregation and set analysis

rajkapoor1 — Mon, 16 Apr 2018 14:22:46 GMT

In other words, this measure is just to say that a store has been open from 01/01/2018 till the 04/02/2018.