Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajkapoor1
Contributor III
Contributor III

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
21 Replies
sunny_talwar

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

rajkapoor1
Contributor III
Contributor III
Author

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.

sunny_talwar

Had typos... try this

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

Capture.PNG

rajkapoor1
Contributor III
Contributor III
Author

Works perfectly! Thanks Sunny!

sunny_talwar

Super

rajkapoor1
Contributor III
Contributor III
Author

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,

RK

sunny_talwar

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

rajkapoor1
Contributor III
Contributor III
Author

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

sunny_talwar

It should

rajkapoor1
Contributor III
Contributor III
Author

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