Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
keitel2015
Contributor III
Contributor III

Count negative customer and display the last 12 month

Hi,

I am looking for the correct formula for my problem. I want to create a bar chart which displays the number of negative customer (based on profit). The bar chart should displays the last 12 months. My fields are: [Fiscal Date], [Customer ID] and Profit. I have a solution if I added the [Customer ID] dimension too but I need a solution without [Customer ID] dimension. My current formula is this: Count({1} DISTINCT [Customer ID]).

test.PNG

1 Solution

Accepted Solutions
sunny_talwar

Try this, seems to be working:

=Count(DISTINCT If(Aggr(Sum(Profit), [Fiscal Date], [Customer ID]) < 0, [Customer ID]))

Capture.PNG

View solution in original post

11 Replies
Gysbert_Wassenaar

It's not clear to my what you're trying to count. The customers that had a negative profit in any of the last 12 months or the customers that have a total negative profit over the last 12 months. Perhaps you can post a qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
keitel2015
Contributor III
Contributor III
Author

Hi,

I want to count customer who have a negative profit over the last 12 month. For example if a customer has a positive profit in the last 3 month but the total over the last 12 month is negative then count. I have the 12 month value in my table (based on your n-period posting). This means in the screenshot above you can see the customer profit accumulated over the last 12 month. My target is it to create a bar chart which displays the last 12 month with the number of customers who are negative. For example in the screenshot above you could see that Jun 2015 has 12 times the values "1". The bar should display Jun 2015 with 12. The month before should display 8 (based on the screenshot) and so on for the last 12 month. I think I need a cumulated dimension (for the last 12 month) and a formula which counts the negative profits based on customer without dimension customer. Is it now clear what I need?

Gysbert_Wassenaar

Ok, that sounds like you need something like this:

Count({1<[Customer ID]={"=sum({<[Fiscal Date]={">$(=addyears(max([Fiscal Date]),-1 ))<=$(=max([Fiscal Date]))"}]>}Profit)<0"}>} DISTINCT [Customer ID])


If that doesn't work or you can't adapt it to your document then post a qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
keitel2015
Contributor III
Contributor III
Author

Hi,


it doesn´t work...

RedSky001
Partner - Creator III
Partner - Creator III

What would you expect to see if the data was just for customer JJJ ?

(I don't fully understand what you're trying to do)

keitel2015
Contributor III
Contributor III
Author

If I had only Customer JJJ then I would expect the value 1 per month if the profit is negative for the month.

You can see in my screenshot a few fiscal dates (e.g. Jun and may 2015) the customer in this period and the profit. The profit in the screenshot is already the rolling 12 month profit (e.g. last row: Jun 2015 UUU -18155; this means that the customer UUU has over the last 12 month a profit of -18155 euro. I want to count all customers per fiscal date with a negative profit (e.g. customer UUU in Jun 2015). After that I want to analyze the last 12 month this means that I want only display the number of customers who has a negative profit for the last 12 month (e.g. bar chart displays only may 2014 until Jun 2015 and not e.g. Jan 2014 because of too old). Is it now clear? 

RedSky001
Partner - Creator III
Partner - Creator III

Customer FFF in 1.Jun.2015has a figure of -21,853 on your screen shot.

Are you saying this figure is the total profit from May 2014 to June 2015 ? 

if so this customer needs to be counted as it's negative, is that correct ?  (your calc is showing 0)

sunny_talwar

Try this, seems to be working:

=Count(DISTINCT If(Aggr(Sum(Profit), [Fiscal Date], [Customer ID]) < 0, [Customer ID]))

Capture.PNG

keitel2015
Contributor III
Contributor III
Author

Yes, you understand what I need. I do not know why QlikView shows 0 for customer F it should be 1. I created one more screenshot which shows the monthly profit values (there is no accumulation). As you can see customer U has -18155 over the last 12 month so the profit value for Jun 2015 (in my first screenshot) is -18155 euro therefore he is one of my negative profit customers for Jun 2015 (but there are 12 more in Jun).

custumer U.PNG