Dates in set analysis

[Anonymous]

Hi All,

I have requirement where in I have to get the count of  a dimesion w.r.t to one date based on the selection of a different date dimension

Please find the sample data

Customer	Effective date	Expiration date
A	6/1/2017	6/1/2018
B	7/2/2017	7/2/2018
C	6/3/2017	6/3/2018
D	8/4/2017	8/4/2018
E	8/5/2016	8/5/2017
F	8/5/2017	8/5/2018
G	8/12/2017	8/12/2018

now we have the month and year filter selections based on effective date.

So the requirement is when we click on effective date we should get count of how many customers are having effective date as well as how are expiring.

for eg: if we click on Aug 2017 we should get count as 1 as we have customer E expiring in aug 2017 and another test box with count of customers having aug 2017 as effective date which will 3

Month and year filter based on effective dates.

So we have two text boxes

Count of customers expiring                                   count of customer effective

Kindly let me know if requirement is unclear

Thanks,

anupama jagan

[maxgro]

Look at the attachment

[Anonymous]

Thanks a lot for your solution. It works great !!. Just a small doubt Is it possible to show count(customer) effective (in expiring customers text box) when no selections are made?

[maxgro]

You can use the max function to count the customer of the max year and max month.

count({$ <eff_year=,eff_month=,eff_date=,exp_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

count({$ <eff_year=,eff_month=,eff_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

[mariusz_kumansk]

HI, Like this? you should think about creating Master Calendar in your application.

[Anonymous]

Hi Massimo,

Is it possible to get ytd and qtd for this?

count({$ <eff_year=,eff_month=,eff_date=,exp_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

[maxgro]

try wity

yearstart or quarterstart

today - 1

[Anonymous]

Hi Massimo,

the expression is working well when we select single year and month, is it possible to extend this one for multiple selections?