2 Replies Latest reply: Oct 5, 2017 3:28 PM by Robert Quin RSS

    Qlikview - Edit Background Color for All Expressions at Once

    Robert Quin

      My dashboard has 40 sheets, each with its own pivot table including 10-30 expressions per pivot table. I am trying to do a mass color edit. Is it possible to loop through all of these with a macro do change the color background for each expression? The goal is to get the subtotals in all pivot tables to be a particular color.

       

      I am currently using this function in the color background under expressions.

       

      IF(ColumnNo() = 0, 

           RGB(141, 180, 226))

        • Re: Qlikview - Edit Background Color for All Expressions at Once
          m w

          This thread contains a macro that colors the backgrounds of all dimensions in a chart. Maybe you can modify it for expressions. You would need to add a loop to get all charts instead of just the one.

            • Re: Qlikview - Edit Background Color for All Expressions at Once
              Robert Quin

              I already solved it.

               

              Sub ChangeColors

               

              set allValues = ActiveDocument.Fields("Variable").GetPossibleValues

               

              msgbox allValues.Count

               

              For i=6 to allValues.Count-1

                  set testing = ActiveDocument.GetSheetObject(allValues.Item(i).Text + " Pivot").GetProperties.Expressions '.Item(0).Data.ExpressionDat

                 

                 

                  msgbox allValues.Item(i).Text + " Pivot"

                 

                  expressionCount = testing.count - 1

                 

                  For iteration = 0 to expressionCount

                      testing.Item(iteration).Item(0).Data.AttributeExpressions.BkgColorExp.Definition.v = "$(Subtotal_Blue)" ' $(Subtotal_Blue)= IF(ColumnNo() = 0, RGB(141, 180, 226)

                  Next

                 

                  ActiveDocument.GetSheetObject(allValues.Item(i).Text + " Pivot").SetProperties testing

              Next