Forum Discussion
Calculate won and lost customers for SAAS data
Hi,
If i understand you correctly you this is the logic:
If all the rows of a customer has a lost date the customer is considered lost. We use the most recent lost date as the official date for that customer.
If there is a single row that does not have a lost date the customer is still won. Here we use the first existing won date as the official date.
If this is the case i believe the first step is to calculate if each customer is lost or won. I think this calculated column should do the trick:Column = IF( divide( Calculate( Countrows('Table') ; all('table') ; [customer] = earlier([customer])) ; Calculate( Countrows('Table') ; all('table') ; [Lost time] <> BLANK() ;[customer] = earlier([customer])) ; BLANK() ) = 1 ; "Lost" ; "Won" )
Br,
JGot it. Two questions:
1. Why did you do this in a calculated column and not a measure? Would it be possible to do the same as a measure? (Just curious)2. What is the BLANK() part in the formula for?
1. Measures are generally intended for dynamic calculations, while calculated columns should be used for more static calculations. Whether what you want to calculate is dynamic and prone to change depending on the context you need to use a measure. If you are categorizing something or defining aspects that you know can only be in one way, you should use custom or calculated columns.
2. THE BLANK() is the 3rd argument of the DIVIDE() syntax. What you're basically doing is saying that if you get "Infinity" as a result of the split the result should be BLANK() instead.
Community ChampionHi,
If i understand you correctly you this is the logic:
If all the rows of a customer has a lost date the customer is considered lost. We use the most recent lost date as the official date for that customer.
If there is a single row that does not have a lost date the customer is still won. Here we use the first existing won date as the official date.
If this is the case i believe the first step is to calculate if each customer is lost or won. I think this calculated column should do the trick:
Column =
IF(
divide(
Calculate( Countrows('Table') ; all('table') ; [customer] = earlier([customer]))
;
Calculate( Countrows('Table') ; all('table') ; [Lost time] <> BLANK() ;[customer] = earlier([customer]))
;
BLANK()
) = 1 ; "Lost" ; "Won"
)
Br,
J
thank you for the answer. how does this look at all the deals?
- tex628
On every row the column evaluates the total number of deals for the current customer and divides that with the total number of lost deals for the same customer. If that value is 1 that should mean that the customer is lost.
What result are you getting and what output are you expecting?
Br,
J
Got it. Two questions:
1. Why did you do this in a calculated column and not a measure? Would it be possible to do the same as a measure? (Just curious)2. What is the BLANK() part in the formula for?
- tex628
1. Measures are generally intended for dynamic calculations, while calculated columns should be used for more static calculations. Whether what you want to calculate is dynamic and prone to change depending on the context you need to use a measure. If you are categorizing something or defining aspects that you know can only be in one way, you should use custom or calculated columns.
2. THE BLANK() is the 3rd argument of the DIVIDE() syntax. What you're basically doing is saying that if you get "Infinity" as a result of the split the result should be BLANK() instead.