# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor II

## SET ANALYSIS TO COUNT EVENTS BY MONTH/YEAR IN CHART

Hello i have a a question about creating  a set analysis in a chart. I would like not to have to make changes to the load script of the underlying data, and if possible achieve this only through a set analysis in a chart.

Basically what i'm trying to do is find the distinct count of ID that have greater than 60 in sales for more tan 3 days in any Month /Year.

here is some sample data...

 ID SALESDATE Sales A 1/1/2017 60 A 1/2/2017 60 A 1/3/2017 10 A 1/4/2017 10 A 1/5/2017 10 A 2/1/2017 60 A 2/2/2017 60 A 2/3/2017 60 A 2/4/2017 10 A 2/5/2017 10 B 1/1/2017 80 B 1/2/2017 80 B 1/3/2017 80 B 1/4/2017 10 B 2/1/2017 10 B 2/2/2017 10 B 2/3/2017 10 B 2/4/2017 80 C 1/1/2017 10 C 1/2/2017 10 C 1/3/2017 100 C 1/4/2017 10 C 2/1/2017 60 C 2/2/2017 60 C 2/3/2017 60 C 2/4/2017 60

what i'm wanting is a calculation that would tell me  that  in 1/2017 only 1 id had >50 in sales for more than 3 days and in 2/2017 two distinct ID's had this.

i'm thinking somthing like this...but its not quite working.

Count({<ID={"=COUNT(SALESDATE)>=3"} , SALESTOTAL={">50"}>}DISTINCT ID)

Labels (3)

• ### Set Analysis

3 Replies
Highlighted
Specialist III

One Solution is:

``````tab1:
LOAD *, ID&Date(SALESDATE,'MMYYYY') As Key INLINE [
ID, SALESDATE, Sales
A, 1/1/2017, 60
A, 1/2/2017, 60
A, 1/3/2017, 10
A, 1/4/2017, 10
A, 1/5/2017, 10
A, 2/1/2017, 60
A, 2/2/2017, 60
A, 2/3/2017, 60
A, 2/4/2017, 10
A, 2/5/2017, 10
B, 1/1/2017, 80
B, 1/2/2017, 80
B, 1/3/2017, 80
B, 1/4/2017, 10
B, 2/1/2017, 10
B, 2/2/2017, 10
B, 2/3/2017, 10
B, 2/4/2017, 80
C, 1/1/2017, 10
C, 1/2/2017, 10
C, 1/3/2017, 100
C, 1/4/2017, 10
C, 2/1/2017, 60
C, 2/2/2017, 60
C, 2/3/2017, 60
C, 2/4/2017, 60
];
``````

Column1: Count(TOTAL <ID,Key> ID)
Column2: Count({<Sales={">50"}>}TOTAL <ID,Key> ID)
Column3: Count({<Sales={">50"}>}TOTAL <ID,Key> ID)>=3

Highlighted
Contributor II

thank you for your reply. my goal is create a chart that looks like below , i'm just not sure how to write an expression that will count Distinct ID for each month that had more than 50 in sales for more than 3 days.  i'm not sure how to convert your solution into that.

 Jan-17 17-Feb Distinct Id with Sales greater than  50 for more than 3 days 1 2
Highlighted
Specialist III

My Script solution will be:

``````tab1:
LOAD Key, Count(DISTINCT ID) As ID_Cnt
Where Dt_Cnt>=3
Group By Key;
LOAD Key, ID, Count(DISTINCT SALESDATE) As Dt_Cnt
Where Sales > 50
Group By Key, ID;
LOAD *, Date(MonthStart(SALESDATE),'MMYYYY') As Key INLINE [
ID, SALESDATE, Sales
A, 1/1/2017, 60
A, 1/2/2017, 60
A, 1/3/2017, 10
A, 1/4/2017, 10
A, 1/5/2017, 10
A, 2/1/2017, 60
A, 2/2/2017, 60
A, 2/3/2017, 60
A, 2/4/2017, 10
A, 2/5/2017, 10
B, 1/1/2017, 80
B, 1/2/2017, 80
B, 1/3/2017, 80
B, 1/4/2017, 10
B, 2/1/2017, 10
B, 2/2/2017, 10
B, 2/3/2017, 10
B, 2/4/2017, 80
C, 1/1/2017, 10
C, 1/2/2017, 10
C, 1/3/2017, 100
C, 1/4/2017, 10
C, 2/1/2017, 60
C, 2/2/2017, 60
C, 2/3/2017, 60
C, 2/4/2017, 60
];``````