I have a table with for every customer a record with (amongst others) all months of a year and the month of subscription
example
customnumber month month of subscription
1 201201 201201
1 201202 201201
1 201203 201201
1 201204 201201
1 201205 201201
2 201203 201203
2 201204 201203
2 201205 201203
etc.
I use next expression :
If ([month_subcribe]= [month of subscription] then (count(distinct [customnumber]) else (0)
When I create a report with the colums month and number of new subscriptions
I only get a result for the first month, so the result for 201201 = 1 but the result for 201303 = 0
What expression do I have to use to get correct results ?
Thanks in advance
I suppose that a customer can't subscribe twice in the same month, so the number of new subscriptions could be defined like this :
Total( If ([month_subcribe]= [month of subscription] then (1) else (0) )
@hittony : Indeed a customer can not subsbribe twice in the same month, but he can take two subscriptions in the same month. I want the customer counted once.
In the mean time I found a solution. I created an extra field indicating whether a customer subscribes (=1) and then counted distinct customnumber where new field =1