Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Find status for a customer

Hi guys,   I am trying to build a report that can tell me whether a customer is new / current / lost. This depends on the when they last ordered something at the company. I thought I had it figur...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Pragati11 ,

 

it is actually part of a summarize table, that's why it might look a little funny.

 

But I made some sample data in excel which I hope might help. The idea is that any costumer that didn't order after 2017 is considered a lost costumer. If they ordered between 2017 and 2020 they are current and if they didn't order in 2018 or 2019, but they did in 2020 they are considered new.

 

https://iblsoftware1-my.sharepoint.com/:x:/g/personal/queenie_barends_truelime_nl/EXobgfCzdBVNs0EIncGtID0Bw0DQaWNt4x1V5XIwlTt2sw?e=4C8nD8

 

Let me know if you need anything else.

 

Kind regards,
Queenie

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , Assume you have 4 different column , then you need to have something like this

 

Switch(true(),
not(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "New"	,	
not(isblank([Sales_2020])) && (not(isblank([Sales_2019])) || not(isblank([Sales_2018]))), "Current"	,	
(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "Lost"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak ,

     

    It is 4 different columns, but it is grouped by reservation date. This means that I have multiple records for one year and that is probably why my first idea doesn't work.

     

    Kind regards