Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
)
)
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.
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.
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
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