3 Replies Latest reply: Jul 8, 2011 12:16 PM by Bikash Debnath RSS

    Set analysis expression comparision

      Hi Everyone,

      I have a new problem to discuss with you.

       

      I have attached a screenshot of the problem. Hope thats self explanatory.

      As you could please see in the attached screenshot, i have two columns

      One is "Number of Pending Opps" whose expression is

      count({$<FACT_TYPE={"Baseline"},LINE_ITEM_STATUS={'Pending'},DATE_SK={$(vBaselineDayKey)},FLAG_HEALTH_REPORT={1}>}OppID )

      (From a previous snapshot)

       

      and the second column is "Number of DIN/DWIN Opps" and whose expression is

      count({$<FACT_TYPE={"Current"},LINE_ITEM_STATUS={'Design In', 'Design Win'},FLAG_HEALTH_REPORT={'1'}>}OppID)

      (current snapshot)

       

      Now I have to find a third column (in my case "Matched" is the column) where it should fetch me all the items(from previous fiscal years) that has converted from Pending to DIN or DWIN(as of current day).

      And I have two QVDs which stores the data every week. So whatever data is from previous week & before is termed as Baseline and the data as of today/(current week) is termed as Current.

       

      For example when Country= Belgium, then out of 8 Pending Opps(as of April 2011) only 3 got converted to DIN/DWIN(as of today).

      And the user wants this view


      And i want to hide rest of the columns.

      Please help me how to do this,

      Header 1Header 2
      CountryNumber of Item converted
      Belgium3

       

       

      Many many thanks,

       

      Bikash

        • Re: Set analysis expression comparision
          Miguel Angel Baeyens de Arce

          Hello Bikash,

           

          If I understood your problem right, you need to get all records from previous year where LINE_ITEM_STATUS equals to "Pending" AND all records from this year there LINE_ITEM_STATUS equals to "Design In" or "Design Win". I don't know your actual fields and possible values, but you will likely need two set modifiers and use both of them:

           

          Count({< FACT_TYPE = {"Baseline"}, LINE_ITEM_STATUS = {'Pending'}, DATE_SK={$(vBaselineDayKey)}, FLAG_HEALTH_REPORT = {1}>
          + < FACT_TYPE = {"Current"}, LINE_ITEM_STATUS = {'Design In', 'Design Win'}, FLAG_HEALTH_REPORT={'1'} >
          } OppID)
          

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Set analysis expression comparision

              Hi Miguel,

              Thanks for your response.

              But I am sorry that wasn't what I wanted.

              I want to actually find the common items.

              I mean items those were in Pending in previous fiscal year, but now some of thise pending items are converted to design in or design win stage.

              Say in April 2011 we had 100 items those were in pending stage for Belgium. And out of those 100, say only 10 got converted to Design in or Design win, so the view should be

              Country:  Matched:

              Belgium   10

               

              Hope now you could help me,

              Thanks,

              Bikash

                • Re: Help needed - Set analysis expression comparision

                  Hi Miguel,

                  Thanks for your earlier post to this issue.

                  I want to attach a sample dataset for my application but I am not sure how?

                   

                  If you can please turn the filters on and select End Customer Country as Vietnam,

                  You will see that 5 items got converted from Pending to Design In or Design Win. Which means it was in pending in a previous snapshot data

                  and the now the current status of those item has changed to Design In/Design Win.

                   

                   

                  Header 1Header 2
                  67214/9FG108DGLFBaseline to Current
                  67214/89HPES3T3ZBBCGBaseline to Current
                  71849/844441DGILFTBaseline to Current
                  71851/8402AYILFBaseline to Current
                  71851/85102AGILFTBaseline to Current

                   

                   

                  Could you please tell me how to solve such issue.

                   

                  Thanks a lot,

                  Bikash