4 Replies Latest reply: Dec 11, 2017 3:30 PM by Lech Miszkiewicz RSS

    NPrinting - adding formulae in Powerpoint

    Paul Ripley

      Hi

      I am using NPrinting to produce to some accounts.

      Each number is made of formula eg AM_01 is a formula

      AM_03 is a subtotal of the numbers above.  I would like to really say AM_03 is AM_01+AM_02

      However I don't seem to be able to do this. and so the formula becomes rather long for Subtotals eg see below

      I need to use floor to round properly, and I also ne to use format  (123) to show negatives

      Is there anyway to simplify the subtotal formulas?

      Thanks

      Paul

      IF(

      (floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {A01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      +
      floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {B01,J05}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      )

      < 0,

      '('&(floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {A01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      +
      floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {B01,J05}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      )

      &')',

      (floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {A01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      +
      floor(sum({$<period = {$(vEndPeriod_CY)}, pl = {B01,J05}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1
      )
      )

        • Re: NPrinting - adding formulae in Powerpoint
          Lech Miszkiewicz

          If you are referning to NPrinting formulas then there is no "shorter" way as each formula is independent.

          You may levarage Qlik variables and do calculation there. That would simplify the process.

           

          on the other note:

          this is not long formula - i have formulas which span accross 250 lines, so what you are doing is completly OK

           

          cheers

          Lech

          ---------------------------------------------

          When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

            • Re: NPrinting - adding formulae in Powerpoint
              Lech Miszkiewicz

              Another comment regarding (123) for negative values

              You don't need to use IF statement, instead use num () function with format '#,##0.0; (#,##0.0)', where first format will automaticly be  applied if result is greater than 0 and second format for values < 0

              as an option you can put value for result = 0

              like:

              num( Sum(Sales), '#,##0.0; (#,##0.0), no sales'),

               

              I also would avoid using floor on each level and would try to stick to num() function first.

               

              That should make it much simpler, right?

              I think you should just have (I am not sure why you did..... +500000,1000000)/1000000?? - I gues you will figure this out). Given that you don't need IF statement and you will only use num() your formulas will be simpler

               

              num (

                             sum({$<period = {$(vEndPeriod_CY)}, pl = {A01}>} IS_Investment_GBP)

                             +

                             sum({$<period = {$(vEndPeriod_CY)}, pl = {B01,J05}>} IS_Investment_GBP) ,

                         '#,##0.0; (#,##0.0)'    

                        )


              or (if you need to devide by: say' 1000000


              num (

                             sum({$<period = {$(vEndPeriod_CY)}, pl = {A01}>} IS_Investment_GBP) /1000000

                             +

                             sum({$<period = {$(vEndPeriod_CY)}, pl = {B01,J05}>} IS_Investment_GBP) /1000000,

                         '#,##0.0; (#,##0.0)'    

                        )



              cheers

              Lech

              ---------------------------------------------

              When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.