How to count new /lost entities between two periods

[Anonymous]

Hello!

I have a question: hope someone can find a solution!

Let consider the following table:

Year	Customer
2012	A
2012	B
2012	C
2012	D
2013	A
2013	B
2013	E
2013	F
2013	G
2014	A
2014	B
2014	E
2014	H
2014	I
2014	L
2014	M

My application has a listbox for Year.

I wuold like to count (in 3 simple text boxes):

1- N° of customer in the selected year;

2- N° of new customer in the selected year respect to previous year;

3- N° of "lost" customer in the selected year respect to previous year.

Examples:

Selection of Year 2013:

1- N°=4

2- N°=3

3- N°=2

Selection of Year 2014:

1- N°=7

2- N°=4

3-N°=2

I tried to use AGGR function without success.

Thanks in advance!

Elena

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[Anonymous]

Thank you very much!

It works perfectly!

KR,

Elena

[Anonymous]

Hi!

A new question regarding the same configuration.

Let's suppose we have a straight table with Customers and their Orders for current and previous year.

I would like to select all New Customers or all Lost Customers by clicking on a button: the table must update coherently.

So, I should create two buttons with a Trigger for:

-selecting all new customers: in this way orders for previous year will be null and there should not be any problems;

-selecting all lost customers: in this way I think that there is a problem because of the current selection of the year... if they are lost, they won't appear at all.

Is there any solutions?

Thank you!

Elena

[Gysbert_Wassenaar]

You can use the Conditional option to show/hide expressions in a straight table as needed. See attached qvw.

[Anonymous]

Thank you Gysbert!

I understand that it's not possible to make a direct selection of these two groups in order to get filters in other listboxes which may be insert in a more complex scenario.

Thanks!

Elena