3 Replies Latest reply: Dec 2, 2015 10:00 AM by Jonathan Poole RSS

    if , then sum , with formatting

    Joanna Seldon

      Hiya

       

      1. I need to dynamically change my formatting depending on the calculation

       

      2. only when a certain department number is chosen, it completes a calculation

       

       

      I have the following expression

       

      if (Department = 1,

      (num( sum([VALUE])/sum([OUTOF]), $(= chr(39) & $(=$(vFormat)) & chr(39)),     //Percentage calculation

      if (Department = 2,  (sum([FINALVALUE]), $(= chr(39) & $(=$(vFormat)) & chr(39)   //straight number

      )

      )

       

       

      I cannot get it to work

       

      please help

        • Re: if , then sum , with formatting
          Jonathan Dienst

          Your statement is syntactically incorrect (unbalanced parentheses and too many options for the if statement), and an unknown variable (vFormat) so it is hard to help you base on what you have posted.

           

          Please provide a detailed explanation of what you are trying to achieve, provide the definitions of all variables involved and (best of all) a sample qvw file with representative data.

            • Re: if , then sum , with formatting
              Joanna Seldon

              Hiya

               

              the vFormat is declared in my data load

               

              set vFormat='if(Only(Format)=' & chr(39) & 'Integer' & chr(39) &',' &chr(39)& '0' & chr(39) & ',' & chr(39) & '#.##%' & chr(39) & ')';

               

               

              without the formatting - main aim is for the calculation to work

              I have tried just to have this, but this does not work too

               


              if (Department = 1,

              sum([VALUE])/sum([OUTOF]),

              if (Department= 2,  sum([FINALVALUE])

              )

               

               

              please help

                • Re: if , then sum , with formatting
                  Jonathan Poole

                  Joanna , here is a sample. You can use num() to do the conditional formatting of the measure and have it change on a table row by row basis in the table object, just leave the number formatting on the right to 'auto' . Does this help ?  see if you can get a hand written sample working as below, then we can work on the variable syntax.

                   

                  Untitled.png

                  Capture.PNG