4 Replies Latest reply on Jun 16, 2012 7:48 AM by Elizabeth Coleman

    IF Statement

    Elizabeth Coleman

      I'm trying to count the number of distinct sales orders in a month.  For instance I have 3 sales orders that have 1 number assigned to it for the same customer B with different amount of revenue on each of them.  My current formula counts each instance of that sales order.  Here's is my formula:  IIF([Type] ="F", 1, 0).  What changes do I need to make to the formula so it only counts the sales order once if there are more than instance in a given month?  I tried changing the measure to CountD - that's doesn't work because it only counts the sales order once even if it was in an earlier month.  I want to count the sales orders each month it showed up in my data. 

        • 1. Re: IF Statement
          Alex Kerin

          Perhaps you could create a unique sales order/month variable.

           

          Something like countd([Order]+str(datetrunc('month',[Date])

          1 of 1 people found this helpful
          • 2. Re: IF Statement
            Elizabeth Coleman

            That was helpful but didn't quite give me the results that I'm trying to accomplish.  I'm using custom to combine two tables; orders that were forecasted to invoice and orders that moved out of the planned month.  The formula needs to include both tables.  Here is my sample SQL:

            SELECT
              [ForecastCurrentMonth$].[Planned Start of Month] AS [Test Date],
              [ForecastCurrentMonth$].[Sales Order] AS [Sales Order],
              [ForecastCurrentMonth$].[Equip $] AS [Equip $],
              [ForecastCurrentMonth$].[VAS $] AS [VAS $],
              [ForecastCurrentMonth$].[Region] AS Region,
              [ForecastCurrentMonth$].[CCT] AS CCT,
              [ForecastCurrentMonth$].[Proj Mgr] AS [Proj Mgr],
              [ForecastCurrentMonth$].[OMS] AS OMS,
              [ForecastCurrentMonth$].[Order Creation Date] AS [Order Creation Date],
              [ForecastCurrentMonth$].[Fiscal Month] AS [Fiscal Month],
              [ForecastCurrentMonth$].[BU] AS BU,
             
              "F" as [Type]

              FROM [ForecastCurrentMonth$]

               UNION ALL

            SELECT
              [MovedOut$].[Invoice Date] AS [Test Date],
              [MovedOut$].[Sales Order] AS [Sales Order],
              [MovedOut$].[MovedOutEquip$] AS [Equip $],
              [MovedOut$].[MovedOutVAS $] AS [VAS $],
              [MovedOut$].[Region] AS Region,
              [MovedOut$].[CCT] AS CCT,
              [MovedOut$].[Proj Mgr] AS [Proj Mgr],
              [MovedOut$].[OMS] AS OMS,
              [MovedOut$].[Order Creation Date] AS [Order Creation Date],
              [MovedOut$].[MovedOutMonth] AS [Fiscal Month],
              [MovedOut$].[BU] AS BU,
             
              "M" as [Type]

            FROM [MovedOut$]

             

            The SQL works fine but I need to be able to count the distinct sales orders in a month for each table.  I think a IF statement is the way to do it but I don't know how to write the statement.  I've attached a sample file.

             

            Message was edited by: Elizabeth Coleman

            • 3. Re: IF Statement
              Alex Kerin

              You are not counting against two tables - it becomes one result in Tableau, so the SQL is not relevant. I don't understand what result you want in what sheet.

               

              At a guess, would

               

              countd(if [Type]=F then [Order]+str(datetrunc('month',[Date] else Null end)

               

              produce what you want?

              • 4. Re: IF Statement
                Elizabeth Coleman

                I agree the SQL is irrevelant to what I'm trying to achieve. 

                 

                I tried the formula you suggested, I got a message 'the calculation contains errors'.