Hi Arole,

yes I tried a lot but I have nothing. Indeed, I think the most important is to crate a calculated field "new business vs recurring" with 1 if it is a new business or 0 if it is not (to flag the idrevenue). And for this calculated field, I don't know how to translate with formula the fact an idRevenue is new i.e for the same client there is no other idRevenue with a Running Date in the 3 last months before the beginning of this idrevennue.

I could give you

Thanks,

Christophe

sorry i didn't finish my post: I could give you a .twbx with the excel I gave you but I have currently no idea for the calculated field "new business vs recurring"

Thanks

Hello Luciano,

I think your are quite close to obtain if an 'idrevenue' is 'New Business' or 'Recurring' but there is just a little correction to do to obtain this:

If you look at my pptx presentation:

your results are good for all "idrevenue" except : 676, 1282 and 1923

In fact you need to add a condition in your field ''New Business or Recurring" in your "if condition" to take in account there is no "Running date" before.

If you look at my presentation:

" The client is new if it is the first time a client has a campaign running or if there wasn’t any campaign running for this client in the past N months"

After that, the final output I need is the table on the slide 2, do you think it's possible to obtain that with your solution ?

Thank you very much for your help,

Christophe

yes it's correct! Thanks

Do you think you can obtain the output presented in the ppt?

Here is the output:

details of calculations are presented in the last slide:

here are the details of the calculations for this table (according to the excel) (to understand look at the slide 2 of the ppt in the same time):

January

New business : 0

Running : 0

February

New business : 0

Running : 0

March

New business : 0

Running : 0

April

New business : 676/1282 => 1+6

Running : 0

May

New business : 676/1282/1923 => 4+1+9

Running : 0

June

New business : 676/1282/1923 => 3+3+2

Running : 1273 => 4

Jully

New business : 676/1923 => 2+0

Running: 1273 => 1

August

New business : 1923 => 0

Running : 1273/1836 = > 8+3

September

New business : 1923 => 6

Running : 1273/1836 => 2+6

October

New business : => 0

Running : 1836 => 1

November

New business : 0

Running : 1836 : 2

December

New business : 0

Running : 1836 = >7

And so on for 2016

Thank you,

Christophe

You wrote :

"I believe you can't achieve this because to calculate i need clients and revenues in presentation."

=> You just need fields in the excel: Client, IdRevenue, Running Date, Revenue (K€).

If you need any further information to understand my problem, let me know.

Thanks

you mean: "you can't" ? or "you can"?

Ok, now I understand very well your problem. Do you think it's possible to obtain expected values no matter if you are forced to keep

Client, IdRevenue, Running Date, Revenue (K€)