topic Counting valid subscriptions on months last day in QlikView

Counting valid subscriptions on months last day

Thu, 31 Oct 2013 07:12:23 GMT

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?

Re: Counting valid subscriptions on months last day

Thu, 31 Oct 2013 07:22:24 GMT

Hi,

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

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

Re: Counting valid subscriptions on months last day

Thu, 31 Oct 2013 08:05:11 GMT

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)

Re: Counting valid subscriptions on months last day

preminqlik — Thu, 31 Oct 2013 09:03:52 GMT

hi mika,

try this

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