Count negative customer and display the last 12 month

[keitel2015]

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]).

[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.

[keitel2015]

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.

[keitel2015]

Hi,

it doesn´t work...

[RedSky001]

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]

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]

Customer FFF in 1.Jun.2015

has 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 · Accepted solution]

Try this, seems to be working:

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

[keitel2015]

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).