Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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...

IDSALESDATESales
A1/1/201760
A1/2/201760
A1/3/201710
A1/4/201710
A1/5/201710
A2/1/201760
A2/2/201760
A2/3/201760
A2/4/201710
A2/5/201710
B1/1/201780
B1/2/201780
B1/3/201780
B1/4/201710
B2/1/201710
B2/2/201710
B2/3/201710
B2/4/201780
C1/1/201710
C1/2/201710
C1/3/2017100
C1/4/201710
C2/1/201760
C2/2/201760
C2/3/201760
C2/4/201760

 

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)
3 Replies
Highlighted
Specialist III
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

commQV07.PNG

Highlighted
Contributor II
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-1717-Feb
Distinct Id with Sales greater than  50 for more than 3 days12
Highlighted
Specialist III
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
];

commQV10.PNG