Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
I have a set of customer IDs in my document.
the ID is only for active customers per month.
the time resolution in my document is a month.
for example, in November I had 1000 customers, in December 10 new customers added and 25 became inactive so, the customers amount in December is 985.
what I wish to have is some object/expression which will show me per each month, which are the customers that became inactive (the 25 customers in December in my example).
is there a way of doing it via set analysis? or only at the script?
please assist,
thanks
Hi Shay,
It's difficult to give you an expression that will work for you while knowing so little of your application but maybe something like this might work for you.
In your application replace EntryMPeriod with your month field and replace 201511 and 201510 with this month and the previous month respectively
=Concat(DISTINCT {$
<Customer = P({<EntryMPeriod = {201510}>})>-<Customer = P({<EntryMPeriod = {201511}>})>
}Customer,',')
In my application this lists customers who placed orders in October but not in November, becoming, by your definition, inactive.
You will need some kind of logic to identify those customer. Maybe something like this:
count({< Customer = {"=sum(sales)>0"}>} DISTINCT Customer)
- Marcus
is there any Flag to recognize inactive customers?
Since you asked about minus operation, guessing you have a flag to active customer. If so, you can try something like:
=Count({<CustomerFlag -={'Active'}>} Distinct Customer) //Note the '-' symbol
Or, if you 'InActive' flag too, you can use simpler option like:
=Count({<CustomerFlag ={'InActive'}>} Distinct Customer)
hi all,
thank you all for your replies.
I don't have any flag for inactive customers and neither an indication like sales value.
I have a source table with aggregated customers data per customer and month. I filter only customers with filed the status equals "Active". hence, I have records for each month. if a customer becomes inactive - it means I'll filter out his data from that month and so on.
by "minus" i meant that i want to implement the minus operation in SQL which allows me to get a record set which included in one data set and do NOT included in another.
is it possible with set analysis? or i need to do it via script only?
Hi,
Would depend on your data.
What defines the types of customer . i.e. Active, Inactive.
Existing customer / new customer.
There will be a way to approach this in the set analysis and script, but would need a better understanding first.
Mark
Hi Shay,
As you said:
"the ID is only for active customers per month."
This means if a user is inactive he will not have any ID, in this case create a expression:
count(Distinct If(isnull(ID)<>-1,CustomerID))
In Dimension:
Month
You can create a Bar or straight chart.
Regards
KC
that's correct BUT i want to get the customer's IDs.
getting how many customers became - that's i know.
the catch is to get their IDs... (concat for example, not count)
hi,
I have a field for active customers.
if a customer is active in month X and inactive in month X+1, than he will have a certain value in that field for month X and null for month X+1
Hi ,
Try this:
If(isnull(ID)<>-1,concat(Distinct CustomerID,';')))
Regards
KC