2 Replies Latest reply: Feb 1, 2013 12:39 PM by Dipti Desai RSS

    How to eliminate Macro code to replace with triggers at Document/Sheet level.

      Hi All,

       

      I'm using QlikView version 10.00.9274.8 SR4 x64

      Need help converting the below macro code into the triggers on the document/sheet. Any help would be greatly appreciated and thanks a lot in advance for your time and input.

       

      The variable 'DocumentLevel' has the definition:

       

      =if(count(DISTINCT ALL [Territory Number])=1,'Territory',

        
      if(count(DISTINCT ALL Region)=1,'Region',  

           
      if(count(DISTINCT ALL Area)=1,'Area',

              
      if(count(DISTINCT ALL [Sales Team])=1,'Sales Team',

                  'All Data'))))

       

      and Variable 'SalesGroupCurrentField' has the definition =GetCurrentField(SalesGroup)

       

      Have created a cyclic group called 'SalesGroup'

       

      ==============================================================

      Macro code to be eliminated and replaced with triggers at document/sheet level

      ==============================================================

       

      Sub SetSalesGroup

           mDocumentLevel =
           ActiveDocument.Variables("DocumentLevel").GetContent.String

           set group = ActiveDocument.GetGroup("SalesGroup")

           set gp=group.GetProperties
                gdims = gp.FieldDefs.Count

                'Clear current fields from group

      for i = gdims-1 to 0 step -1

         group.RemoveField i

         'set gp=group.GetProperties

      next



      if mDocumentLevel = "All Data" then

         group.AddField "Sales Team"

         group.AddField "Total
      Market"

         group.AddField "Area"

         group.AddField "Region"

       
      group.AddField "Territory"



      elseif mDocumentLevel = "Sales Team" then

         group.AddField "Area"

         group.AddField "Total
      Market"

         group.AddField "Region"

         group.AddField "Territory"



      elseif mDocumentLevel = "Area" then

         group.AddField "Region"

         group.AddField "Total
      Market"

         group.AddField "Territory"



      elseif mDocumentLevel = "Region" then

         group.AddField "Territory"

         group.AddField "Total
      Market"


      else group.AddField "Territory"

      end if

      End Sub



      'if SalesGroup cyclic group value is Total Market set PlanTrendMgrs chart to show Trellis as 1x1

      Sub PlanTrendMgrsProperties

           mDocumentLevel =
           ActiveDocument.Variables("DocumentLevel").GetContent.String

           mSalesGroup =
           ActiveDocument.Variables("SalesGroupCurrentField").GetContent.String

           if mDocumentLevel = "Territory" OR mSalesGroup = "Total Market" then

              PTColumns=1

              PTRows=1

           else PTColumns=2

              PTRows=3

           end if

           set chart = ActiveDocument.GetSheetObject("PlanTrendMgrs")

           set p = chart.GetProperties

           p.ChartProperties.TrellisProperties.Columns = PTColumns

           p.ChartProperties.TrellisProperties.Rows = PTRows

           'v10 IR bug; some prop on chart must change to recalculate chart so set Error Bar Color to some random color

              randnum = round(rnd()*255)

       
           p.ChartProperties.ErrorBarColor.PrimaryCol.Col = RGB(randnum,randnum,randnum)

           chart.SetProperties p

      End Sub