Forum Discussion

cnpdx's avatar
cnpdx
Icon for Helper II rankHelper II
9 years ago
Solved

How do I calculate Lost Customer Sales?

  I am trying to calcluate; "what were sales last month of customers who didn't purchase this month?"   This article was amazing, and a real game changer to help calculate returning, new and lost...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    cnpdx

     

    Lookvalue have a result which is not a boolean expression.

     

    In your case, you have to add IF(lookvalue........) <> Blank() 

     

    When the result is blank the formula give you a value and when is not another value ("Y" or "")

     

     

  • Vvelarde's avatar
    Vvelarde
    9 years ago

    cnpdx

     

    Try with this:

     

    IF

    (

       IF(SalesDetails<>12,

                IsBlank(LookupValue(....)),

                IsBlank(LookupValue(....))

    ),

    "Y",""

    )

     

  • cnpdx's avatar
    cnpdx
    9 years ago

    This is all you need:

     

    Lost customer sales = CALCULATE ( [SALES],PREVIOUSMONTH('DATE[DATE]),FILTER(CUSTOMER,[SALES]=0)  

     

     

cnpdx's avatar
cnpdx
Icon for Helper II rankHelper II
9 years ago

Hi Vvelarde

 

Thanks, adding the ) worked, but now I get this new error. does anyone have a solution for that (pic)? The error says it doesn't like the customer key field (text)...

 

 

 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

cnpdx

 

Lookvalue have a result which is not a boolean expression.

 

In your case, you have to add IF(lookvalue........) <> Blank() 

 

When the result is blank the formula give you a value and when is not another value ("Y" or "")

 

 

  • cnpdx's avatar
    cnpdx
    Icon for Helper II rankHelper II
    9 years ago

    i'm just not clear where that IF(lookvalue........) <> Blank() fits in to the whole formula. can you elaborate?

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      cnpdx

       

      Try with this:

       

      IF

      (

         IF(SalesDetails<>12,

                  IsBlank(LookupValue(....)),

                  IsBlank(LookupValue(....))

      ),

      "Y",""

      )