Calculate lost customer

[Carlaf_93]

Good morning,

i applyed this formula to calculate the number of lost customers:

count({$<PeriodCounter=,CLIENT_NAME={'=sum({1<PeriodCounter={"$(=max(PeriodCounter)-1)"}>} [Bill Amount])>0 and (sum({1<PeriodCounter={"$(=max(PeriodCounter))"}>} [Bill Amount])=0 )'}>}distinct CLIENT_NAME)

and it works.

I would like to define the lost ones like this now (if  there is a decline of 50% i define it like lost):

count({$<CLIENT_NAME={'=(sum({1<PeriodCounter={"$(=max(PeriodCounter))"}>} [Bill Amount])/sum({1<PeriodCounter={"$(=max(PeriodCounter)-1)"}>} [Bill Amount]))>0.5 )'}>}distinct CLIENT_NAME)

but it doesn't work.

Ideas?

Thanks.

[Anil_Babu_Samineni]

Try this? and also make sure how the values are returning for inner set analysis so that you will get an idea to change 0.5 to required since there is comparion going on..

count({$<CLIENT_NAME={"=(sum({1<PeriodCounter={$(=max(PeriodCounter))}>} [Bill Amount])/sum({1<PeriodCounter={$(=max(PeriodCounter-1))}>} [Bill Amount]))>0.5 )"}>} distinct CLIENT_NAME)

[Carlaf_93]

Thanks for your reply. 

But this doesn't work. I tryed to see the output of this: sum({1<DATA.autoCalendar.Year={"2021"}>} NETTO)/sum({1<DATA.autoCalendar.Year={"2020"}>} NETTO))>0.5 and is 0.9 so the format seems right.

Any ideas?

[agigliotti · Accepted solution]

Hi @Carlaf_93 ,

Maybe this:
count( {< CLIENT_NAME = {"=(sum( {1< PeriodCounter = {$(=max(PeriodCounter))} >} [Bill Amount] ) / sum( {1< PeriodCounter = {$(=max(PeriodCounter-1))} >} [Bill Amount] ) )>0.5"}>} distinct CLIENT_NAME )

the same @Anil_Babu_Samineni 's expression but without a close bracket.

I hope it can help.
Best Regards

[Anil_Babu_Samineni]

For me this is ok, do you see any issue? can you please share some sample dataset?