Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

paul_ripley
Contributor

NPrinting - adding formulae in Powerpoint

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
)
)

4 Replies
lech_miszkiewic
Honored Contributor III

Re: NPrinting - adding formulae in Powerpoint

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.

lech_miszkiewic
Honored Contributor III

Re: NPrinting - adding formulae in Powerpoint

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.

paul_ripley
Contributor

Re: NPrinting - adding formulae in Powerpoint

Hi Lech

Thanks for taking the time to reply.  Unfortunatley the num function did not work for me (I got a result of 0.0 rather than 41 which I was expecting)

But at least I know there is no easier way to do this!

Many thanks

Paul

lech_miszkiewic
Honored Contributor III

Re: NPrinting - adding formulae in Powerpoint

Hi Paul,

This function has to work - It is the most common solution to such scenrio like yours.

I suggest you play with it little bit, maybe you have made  "typo" in syntax or had something missing.

i guess it is worth reviewing.

cheers

Lech

Community Browser