Grouping for Pivot Table Sub-Totals

[GDunn1]

Hi, my dataset does not group products and I am trying to fix this within Qlik Sense by creating a dimension

=if(Product = 'Mercer Indexed Funds' OR Product = 'Advance Funds (Diversified & Sector)', 'Back Book - Gross Inflows', 'Front Book - FUM')

This was successful, but when I create an If statement to return different types of values for each grouping only one of the groups sub-totals correctly on the pivot table. I think this may be aggr issue, but I cannot seems to find an answer.

num(if(Product = 'Advance Funds (Diversified & Sector)',sum( {<MonthKey={'202406'} >}Inflow )-sum( {<MonthKey={'202403'} >}Inflow ),
if (Product = 'Mercer Indexed Funds',sum( {<MonthKey={'202406'} >}Inflow )-sum( {<MonthKey={'202403'} >}Inflow ),
sum( {<MonthKey={'202406'} >}ClosingFUA )-sum( {<MonthKey={'202403'} >}ClosingFUA )))/1000000,'$#,##0.0 M')

[martinpohl · Accepted solution]

use

sum( {<MonthKey={'202406'} , Product={'Advance Funds (Diversified & Sector)','Mercer Indexed Funds'}>}Inflow )-sum( {<MonthKey={'202403'}, Product={'Advance Funds (Diversified & Sector)','Mercer Indexed Funds'} >}Inflow )+

sum( {<MonthKey={'202406'},Product-={'Advance Funds (Diversified & Sector)','Mercer Indexed Funds'} >}ClosingFUA )-sum( {<MonthKey={'202403'},Product-={'Advance Funds (Diversified & Sector)','Mercer Indexed Funds'} >}ClosingFUA )

Regards

[GDunn1]

Thank you @martinpohl. Perfect!

[GDunn1]

How do I return the result for the following number format? /1000000,'$#,##0.0 M')