Custom Date Filter

[tommyl]

Hello,

I have a problem in creating date filter:

Say, my data is like:

CustomerID	SubscriptionDate	AgeGroup
12345	08.10.2018	Below 12
67890	31.10.2018	25-30

I need to create a date filter for subscription date range. I tried below two options but it didnt work out for me:

The options of mine:

1. Use DateRangePicker:

Problem1: If i select a range, that i have no data belonging to that range, extension does nothing. 

What i expect: It should show the range at the top with  no data in the chart. (attachment "extensionProblem1.jpg")

Eg: According to the above table: Select range: 01.11.2018-04.11.2018 : Nothing has changed. 

Problem2: If i select a range, my date filters are bounded by data's subscription dates. 

What i expect: It should show the range that i selected, not the range of which the data consists of. 

Eg: According to the above table:Select range: 01.10.2018-31.10.2018 , the range turns out to 08.10.2018-31.10.2018.

2.Custom Date Filter

I created two fields in the load editor:

filterStartDate:
LOAD
subscriptionDate as filterStart resident Customer;

filterEndDate:
LOAD
subscriptionDate as filterEnd resident Customer;

Then wrote a set expression like:

count({<subscriptionDate={">=$(=min(filterStart))<=$(=max(filterEnd))"}>}CustomerId) / Count(total{1}CustomerId)

But i did wrong i think, it doesn't work. 

Could you please help me?

[JordyWegman]

Hi Tommy,

For your first problem, you can create a calendar bases on min/max date of you data:

YourTable:
Load
  Max(floor(SubscriptionDate)) as MaxDate,
  Min(floor(SubscriptionDate)) as MinDate 
From [YourSource](qvd);

let vMaxDate = Peek('MaxDate',0,'YourTable');
let vMinDate = Peek('MinDate',0,'YourTable');

Calendar:

Load
  Date(DateNum) as Date,
  Month(DateNum) as Month,
  Year(DateNum) as Year;
Load
  $(vMinDate) + IterNo() - 1 as DateNum
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Add this Date field in the DateRangePicker and you will be able to select your dates.

Jordy

Climber

[tommyl]

Hello Jordy,

Thank you for your help.But, I couldn't manage it. Here is the error i got. 

The following error occurred:
Invalid expression
The error occurred here:
[customer]:
LOAD
[customerId],
[subscriptionSource],

Date(floor([subscriptionDate])) as [subscriptionDate],
Max(floor([subscriptionDate])) as MaxDate,
Min(Floor([subscriptionDate])) as MinDate,
if (isnull(tierType)or tierType = '','UNKNOWN', tierType) as tierType,
if ((isnull(level) or level=''),'UNKNOWN', level) as level,
[status],
if (isnull(gender) or gender ='' ,'UNKNOWN', gender) as gender,

if(len(trim(birthDate))=0,'01.01.1900',birthDate) as birthDate,
[channel],
[createdAt],
[firstName],
[lastName],
[amount],
[countryCode],
[nationality],


APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([countryCode]), '-') AS [customer.countryCode_GeoInfo],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([nationality]), '-') AS [customer.nationality_GeoInfo]
FROM [lib://customer]
(txt, codepage is 1252, embedded labels, delimiter is ';')
The following error occurred:
Invalid expression
The error occurred here:
?

[JordyWegman · Accepted solution]

Hi Tommy,

Do it like this:

[customer]:
LOAD
[customerId],
[subscriptionSource],
Date(floor([subscriptionDate])) as [subscriptionDate],
if (isnull(tierType)or tierType = '','UNKNOWN', tierType) as tierType,
if ((isnull(level) or level=''),'UNKNOWN', level) as level,
[status],
if (isnull(gender) or gender ='' ,'UNKNOWN', gender) as gender,
if(len(trim(birthDate))=0,'01.01.1900',birthDate) as birthDate,
[channel],
[createdAt],
[firstName],
[lastName],
[amount],
[countryCode],
[nationality],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([countryCode]), '-') AS [customer.countryCode_GeoInfo],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([nationality]), '-') AS [customer.nationality_GeoInfo]
FROM [lib://customer]
(txt, codepage is 1252, embedded labels, delimiter is ';')

TableMaxDate:
Load
Max(floor([subscriptionDate])) as MaxDate
FROM [lib://customer]
(txt, codepage is 1252, embedded labels, delimiter is ';')

let vMaxDate = peek('MaxDate',0,'TableMaxDate');
drop table TableMaxDate;

TableMinDate:
Load
Min(Floor([subscriptionDate])) as MinDate
FROM [lib://customer]
(txt, codepage is 1252, embedded labels, delimiter is ';')

let vMinDate = peek('MinDate',0,'TableMinDate');
drop table TableMinDate;

Then you can use the calendar as I have explained.

Calendar:

Load
  Date(DateNum) as Date,
  Month(DateNum) as Month,
  Year(DateNum) as Year;
Load
  $(vMinDate) + IterNo() - 1 as DateNum
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Jordy

Climber

[tommyl]

Hi,

Thank you for your help. Now i wrote a set expression for my chart like: 

Count({$<subscriptionDate = {">=$[$(vMinDate)]<=$[$(vMaxDate)]"}>} customerId)

I cannot understand the mistake, sorry to asking too much