Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

set analysis and minus operation

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

1 Solution

Accepted Solutions
Highlighted
Honored Contributor

Re: set analysis and minus operation

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.

View solution in original post

16 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: set analysis and minus operation

You will need some kind of logic to identify those customer. Maybe something like this:

count({< Customer = {"=sum(sales)>0"}>} DISTINCT Customer)

- Marcus

Highlighted

Re: set analysis and minus operation

is there any Flag to recognize inactive customers?

Highlighted
MVP
MVP

Re: set analysis and minus operation

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)

Highlighted
Contributor

Re: set analysis and minus operation

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?

Highlighted
Partner
Partner

Re: set analysis and minus operation

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

Highlighted
Honored Contributor II

Re: set analysis and minus operation

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

Best Regards,
KC
Highlighted
Contributor

Re: set analysis and minus operation

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)

Highlighted
Contributor

Re: set analysis and minus operation

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

Highlighted
Honored Contributor II

Re: set analysis and minus operation

Hi ,

Try this:

If(isnull(ID)<>-1,concat(Distinct CustomerID,';')))

Regards

KC

Best Regards,
KC