COUNTof Values per day in date range

[andrew415]

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?

[JMAROUF]

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 · Accepted solution]

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

[andrew415]

Works perfectly.  Thank you!