Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

rajkapoor1
New Contributor II

PLEASE HELP: sum, aggregation and set analysis

@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 IDMonthDateProduct SaleCity
AJan01/01/201810London
AJan02/01/20185London
AJan03/01/201810London
AJan04/01/20185London
AFeb01/02/201896London
AFeb02/02/201820London
AFeb03/02/201815London
AFeb04/02/201830London
BJan01/01/201861London
BJan02/01/201813London
BJan03/01/201825London
BJan04/01/201821London
BFeb01/02/201896London
BFeb02/02/201820London
BFeb03/02/201815London
BFeb04/02/201830London
Tags (1)
1 Solution

Accepted Solutions

Re: PLEASE HELP: sum, aggregation and set analysis

Had typos... try this

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

Capture.PNG

21 Replies
rajkapoor1
New Contributor II

Re: PLEASE HELP: sum, aggregation and set analysis

stalwar1‌ Could you please help here? Many thanks!

Re: PLEASE HELP: sum, aggregation and set analysis

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

Or this for all months

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

rajkapoor1
New Contributor II

Re: PLEASE HELP: sum, aggregation and set analysis

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

rajkapoor1
New Contributor II

Re: PLEASE HELP: sum, aggregation and set analysis

Let me try this and get back to you! Thanks

Re: PLEASE HELP: sum, aggregation and set analysis

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

rajkapoor1
New Contributor II

Re: PLEASE HELP: sum, aggregation and set analysis

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

May be this

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

rajkapoor1
New Contributor II

Re: PLEASE HELP: sum, aggregation and set analysis

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 IDMonthProduct SaleCity
AJan30London
AFeb161London
BJan120London
BFeb161London

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,

RK

Community Browser