Embedded RangeMin Function & IF Statements

[Anonymous]

Hello,

I am trying to recreate a pivot table based on different [Cashflow Types] in Qlikview. I am having problems with the following statement. The statement it provides the correct output if I exclude the final IF statement ('Min of Level Shock (a) or (b)’); however, if the statement 'Min of Level Shock (a) or (b)’ is included in its own pivot table it also calculates the correct output. However, when I try to combine it into one pivot table the functions fail. 

If anyone could provide some insights I would really appreciate it.

=sum(
if(TempMortality= 'BELCF - Mortality',
if([Cashflow Type] = 'BELCF - Mortality',
if([Life] = 'Death Supported',
0,
if([Product Type]= 'Mortality Group Life - Not Indiv U/W',
0,
Cashflow*DiscountFactor))
,0)
,if(TempMortality='Level Shock (a)',
if([Cashflow Type] = 'Mortality - Level Shock (a)',
if([Life] = 'Death Supported',
0,
if([Product Type]= 'Mortality Group Life - Not Indiv U/W',
0,
Cashflow*DiscountFactor)
)
,0)
,if(TempMortality= 'Level Shock (b)',
if([Cashflow Type] = 'Mortality - Level Shock (b)',
if([Life] = 'Death Supported',
0,
if([Product Type]= 'Mortality Group Life - Not Indiv U/W',
0,
if([Product Type] = 'Mortality Death Supported - Indiv Others',
0,
Cashflow*DiscountFactor)))
,0)
,if(TempMortality= 'Min of Level Shock (a) or (b)',
Rangemin(
sum(
if([Cashflow Type] = 'Mortality - Level Shock (a)',
if([Life] = 'Death Supported',
0,
if([Product Type]= 'Mortality Group Life - Not Indiv U/W',
0,
Cashflow*DiscountFactor))
,0)
)
,
sum(
if([Cashflow Type] = 'Mortality - Level Shock (b)',
if([Life] = 'Death Supported',
0,
if([Product Type]= 'Mortality Group Life - Not Indiv U/W',
0,
if([Product Type] = 'Mortality Death Supported - Indiv Others',
0,
Cashflow*DiscountFactor)))
,0)
)
)
,0))))
)

[marcus_sommer · Accepted solution]

The problem isn't the range-function it are the sum-functions within thes range-function which is already included in a sum-function.

Such nesting without an aggr-functions isn't possible. I suggest you re-build this expression completely then there are a lot of if-loops which could be written shorter and most of them should be outside from the sum-function. This meant instead of sum(if( you should use if(sum( - see: Sum(if(...)) vs If(Sum(...), Sum(...))

- Marcus