2 Replies Latest reply: Oct 28, 2016 9:59 PM by Briana Nash RSS

    Format value based on another dimension

    Briana Nash

      Hello,

       

      I am somewhat new to Qlik Sense and am setting up a pivot table.  My data is set up in such a way that there is a value field with a number, and an "Attribute" field.  The "Attribute" field is being used to list out the values.  An example of the data is shown below, and I would like to reflect the formatting similarly in the pivot table.

       

        

      AttributeValue
      01: Sales   100,000.00
      02: Cost of Sales     90,000.00
      03: Gross Margin     10,000.00
      04: Operating Expenses       5,000.00
      05: Operating Margin       5,000.00
      06: OM %5.00%
      07: Adjusted %3.00%
      08: Adjustment     (2,000.00)

       

      After playing around with the expression formulas for the value, I have found that it is possible to determine formatting via formula, however I am having trouble with formulating the logic to make it format based on the Attribute.

       

      This is as far as I have gotten, but it is currently reading the condition as false, and thus formatting everything as a percentage.

       

      If(

        Attribute="01: Sales",

         

        Num(Sum(Value)/1000,'#,##0'),

         

          Num(Sum(Value),'#,##0.00%'))

       

       

      Is it possible to format conditionally in a pivot table?

       

      Any thoughts are quite welcome.

       

      Warm Regards,

      Briana

        • Re: Format value based on another dimension
          Oleg Troyansky

          Hi Briana,

           

          the only thing that appears to be wrong in your formula is the use of double quotes for the string '01: Sales' instead of single quotes. So, I'd correct the formula this way:

           

          If(

            Attribute='01: Sales',

            

            Num(Sum(Value)/1000,'#,##0'),

            

              Num(Sum(Value),'#,##0.00%'))

           

          Let me just mention as a side comment that this calculation will get a lot slower than it should be, especially when you add all other options. Qlik Sense will calculate all the possible formulas and show one of the results to you. If your data is very small, than this may not be a concern, otherwise, I'd look for other ways of reaching the same effect.

           

          cheers,

          Oleg Troyansky

          Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business