Counting valid subscriptions on months last day

[#1]

Hi,

I have linked subscription data to mastercalendar, and I'm trying to achieve something like this:

Dim 1 : MasterCalendar.Year

Dim 2 : MasterCalendar.Month

Expr 1 : SUM( Subs.Sales )

Expr 2 : Count( Distinct Subs.Id )

Year	Month	Sales	Active subscriptions
2013	1	255	34
2013	2	343	36
2013	3	856	45

The problem is, that when the business folk talk about "number of subscriptions", they mean the amount of active subscriptions on the last day of the month in question (ie. do not count the subscriptions that ended before the last day of the month).

How do I get count of only the last day instead of the count of the whole month?

I've tried using set analysis

Count( {MasterCalendar.Date = {$(=...get last day of month when year = dim 1 and month = dim 2...)}} distinct Subs.Id)

But as far as I can tell, you can't use dimensions with the set analysis.

How should I solve this?

[#2]

Hi,

Use master calender date for calulation of last day of month.

e.g. =MonthEnd(if(Year='' and Month='',Date))

[#3]

Try the following work around

In the script, create one more table like below

MonthlySubscription:

Load Year,

     Month,

     Max(Date) as LastDay

Resident MasterCalendar GroupBy Year, Month

Left Join (MonthlySubscription) Load

     Date as LastDay,

     Count(Distinct SubsID) as SubscriptionCount

Resident FACT

now in your expression you can use Sum(SubscriptionCount)

[preminqlik]

hi mika,

try this

Count( {<Year={$(=Max(Year)},Month={$(=Max(month)},week={$(=max(week)},Day={$(=max(Day-1)}>}Distinct Subs.Id )