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: 
nlaughton
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
Saravanan_Desingh

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

nlaughton
Contributor II
Contributor II
Author

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
Saravanan_Desingh

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