3 Replies Latest reply: Jul 1, 2011 3:33 PM by Bikash Debnath RSS

    Calculation error need urgent help

      Hi All,

      I now ran into this problem,

       

      I am looking in to past snapshots of data. We have a weekly refresh. My requirement is to find the count of all the items those were in Pending stage between this date range 05/24/2010 to 06/27/2010 has now( as of today) converted to 'Design In' or 'Design Win' Stage. DW_LOAD_DATE is the date field that is used

      to store the snapshot every week.

       

      I have attached the screenshot of the table box where it correctly shows whats happening, but when I use the following syntax it yields zero.

       

       

      Count(if(DW_LOAD_DATE>='05/24/2010' AND DW_LOAD_DATE<=06/27/2010 AND LINE_ITEM_STATUS='Pending',

       

       

         if(U_LINE_ITEM_STATUS='Design In' or U_LINE_ITEM_STATUS='Design Win', OPP_ITEM)))

       

       

      Could you please help me in finding where's the mistake is?

       

      I have also attached the excel file exported from the QV app.

       

      Thanks,

      Bikash

        • Re: Calculation error need urgent help

          Hi All,

          I found where's the mistake is but I need to fix it.

           

          When I hard code the date it works but when I use two variables for the min(DW_LOAD_DATE) and max(DW_LOAD_DATE) it doesn't work.

           

          The expression with the variables looks like this

           

          =Count(distinct {<DW_LOAD_DATE={">=$(vConvStart)<=$(vConvEnd)"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM)

           

          where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

           

           

          Please help,

          Thanks,

          Bikash

          • Calculation error need urgent help

            Is load date being stored as a date or a character string?

             

            Initially, I would say remove the leading zero on the dates and put 6/27/2010 in quotes.

             

            You

              • Calculation error need urgent help

                Hi Alang, Thanks for your response.

                I recently discovered that its the set analysis expression which is actually not calculating the results correctly.

                When I hard code, even with the leading zero it works fine. And Load Date is stored as date.

                I need help in writing the correct expression for this set analysis expression.

                 

                =Count(distinct {<DW_LOAD_DATE={">=$(vConvStart)<=$(vConvEnd)"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM).

                Instead if i write this it perfectly works,

                 

                =Count(distinct {<DW_LOAD_DATE={">=05/24/2010<=06/27/2010"}, LINE_ITEM_STATUS={'Pending'}, U_LINE_ITEM_STATUS={'Design In', 'Design Win'}>} OPP_ITEM)

                 

                where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

                 

                 

                 

                NB*-

                where $(vConvStart)=min(DW_LOAD_DATE) and $(vConvEnd)=max(DW_LOAD_DATE).

                 

                 

                For some reason its not working.