cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dedelman_clng New Contributor
New Contributor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@PaulDBrown

 

I mocked up some detail data, it appears that it is working from my end.  Perhaps my detail was too simplified?

 

CustomerType = 
var TYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), YEAR(Tab[Date])=Year(TODAY())))
var LYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]),  YEAR(Tab[Date])=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

 

date2.PNGdate3.PNG

 

 

 

 

 

 

 

 

 

 

 

David

PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@dedelman_clng

 

HI David,

 

Not sure why, but in my data set the whole column returns the same value: "REPEAT"...I need to invest some time to try to work it out....

 

Thank you for your help on this!

 

Paul.

dedelman_clng New Contributor
New Contributor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

Hi @PaulDBrown - I neglected to mention that I also added YEAR in the date comparison (highlighted below).  Sorry for the oversight.

 

CustomerType = 
var TYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), YEAR(Tab[Date])=Year(TODAY())))
var LYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]),  YEAR(Tab[Date])=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)
Highlighted
PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@dedelman_clng @nairisha

David,

 

Thank you, I did try with the corrected YEAR. What I'm seeing is a single value of "REPEAT" along all lines (this is not surprising when I try the whole current year as per your formula, since my data set only has repeating clients this current year; I'm going to have to change some rows manually to force the data to deliver different outputs and see if it works.

 

However there are "New" and "Lost" clients month by month, and I've tried using the formula including:

YEAR(Tab[Date])=Year(TODAY()) && MONTH(Tab[Date] = MONTH(TODAY())

YEAR(Tab[Date])=Year(TODAY())-1 && MONTH(Tab[Date] = MONTH(TODAY())

 

for the current month but it also delivers "Repeat" as a unique value across all rows, when there are other types of clients in the current month. So I'm going to have to work deep to find out what is going on.

 

BUT there is a bright side to all of this! I subsequently found a workaround (I'm kicking myself for not having thought of this before).... In the report I'm working on for this model, it occured to me to filter the visual displaying columns for [client], [this year sales], [last year sales] and [% difference between both years].

So basically what I've done is (using the Visual Filters and filtering by the % difference column which is a measure using the DIVIDE function):

 

REPEAT;  when % DIFF IS NOT BLANK AND IS NOT -100% (-100% is the value computed when there is a sales value in last year and no result this year)

NEW: when the % DIFF IS BLANK (BLANK is computed when the denominator is 0 within the DIVIDE function)

LOST: when the % DIFF = -100%

 

and there you go!!

 

Not ideal, since it means I have to repeat the process every time I want to do any kind of analyses based on client status, but at least I've got it working!

 

Thank you again for all your help!!

 

Best regards,

 

Paul.