Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
andrew415
Contributor
Contributor

COUNTof Values per day in date range

Hello,

I need to COUNT [# of subscribers] by DAY between a range of dates.

LOAD

    subscriber_name,

   subscription_start_date,

   subscription_end_date

...

 

Filters Panes for Range of Selected dates/periods

Bar Chart of count of subscribers by Day

i.e.  I have 301 subscribers on 01Jan, 305 on 02Jan, 299 on 03Jan, etc.

How can I optimize my data model, organize my bar chart, and write a formula to solve this requirement?

1 Solution

Accepted Solutions
mato32188
Specialist
Specialist

Hi Andrew,

hope this could help you.

First script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

tmp:
load * Inline [
subscriber_name,subscription_start_date,subscription_end_date
a,10/2/2019,11/5/2019
b,10/3/2019,11/5/2019
c,11/1/2019,12/10/2019
d,12/13/2019,12/15/2019
e,12/20/2019,1/5/2020
];


[max/min dates]:
load max(subscription_end_date) as 2, min(subscription_start_date) as 1 Resident tmp;

let maxDate = Peek('2',0,'max/min dates');
let minDate = Peek('1',0,'max/min dates');
drop table [max/min dates];


calendar:
load $(minDate)+IterNo()-1 as Date
AutoGenerate 1 while $(minDate)+IterNo()-1 <= $(maxDate);

inner join(tmp)
IntervalMatch(Date)
load
subscription_start_date,
subscription_end_date
Resident tmp;

Then bar chart:

Dim: =date(Date)

Measure: = count(subscriber_name) or if you want unique count, use count(distinct subscriber_name)

Thanks.

m

ECG line chart is the most important visualization in your life.

View solution in original post

3 Replies
JMAROUF
Creator
Creator

Hi @andrew415 

after verifying your data,  it seems that you have some subscriptions per range (not per day), can you give an example to clarify your goal?

 

mato32188
Specialist
Specialist

Hi Andrew,

hope this could help you.

First script:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

tmp:
load * Inline [
subscriber_name,subscription_start_date,subscription_end_date
a,10/2/2019,11/5/2019
b,10/3/2019,11/5/2019
c,11/1/2019,12/10/2019
d,12/13/2019,12/15/2019
e,12/20/2019,1/5/2020
];


[max/min dates]:
load max(subscription_end_date) as 2, min(subscription_start_date) as 1 Resident tmp;

let maxDate = Peek('2',0,'max/min dates');
let minDate = Peek('1',0,'max/min dates');
drop table [max/min dates];


calendar:
load $(minDate)+IterNo()-1 as Date
AutoGenerate 1 while $(minDate)+IterNo()-1 <= $(maxDate);

inner join(tmp)
IntervalMatch(Date)
load
subscription_start_date,
subscription_end_date
Resident tmp;

Then bar chart:

Dim: =date(Date)

Measure: = count(subscriber_name) or if you want unique count, use count(distinct subscriber_name)

Thanks.

m

ECG line chart is the most important visualization in your life.

View solution in original post

andrew415
Contributor
Contributor
Author

Works perfectly.  Thank you!