Subtotal/ Sub on Pivot with nested If condition

[mja_jami]

Dear Qlik experts,

I’ve this pivot table with row level tow (2) fields; Week and Products. I want to allow users to filet by Products and if no filter used for the product want to show all the products with the sub-total on week level.

With the following it’s working fine when products are chosen the problem is if no product is selected then the sub-total is not adding all, but doing only (count({$<Year={$(=Only(Year)-1)}>}gen_premium).

IF(product_name='AD&D',count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey) - sum ({$<Year={$(=Only(Year)-1)}>} can_add_flag), IF(product_name='DBL',count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey)- sum ({$<Year={$(=Only(Year)-1)}>} canpol_week_testflag), IF(product_name='PFL',count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey) - sum ({$<Year={$(=Only(Year)-1)}>} can_pfl_flag) , IF (product_name <>'AD&D' or product_name<>'DBL' or product_name <>'PFL' , count({$<Year={$(=Only(Year)-1)}>}gen_premium), ))))

I failed and reached to you guys if you can help. How can I achieve the total like in Excel

ExcelMy Qlik shows:Qlik Total

Thank you very much for your help and effort.

[zhadrakas]

you can try it like this:
Change "WEEK" with your Week Field or use Year.

IF(product_name='AD&D',
sum(aggr(count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey) - sum ({$<Year={$(=Only(Year)-1)}>} can_add_flag), WEEK)),
IF(product_name='DBL',
sum(aggr(count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey)- sum ({$<Year={$(=Only(Year)-1)}>} canpol_week_testflag), WEEK)),
IF(product_name='PFL',
sum(aggr(count({$<Year={$(=Only(Year)-1)}>} distinct %PolicyKey) - sum ({$<Year={$(=Only(Year)-1)}>} can_pfl_flag), WEEK)),
IF(product_name <>'AD&D' or product_name<>'DBL' or product_name <>'PFL' ,
sum(aggr(count({$<Year={$(=Only(Year)-1)}>}gen_premium), WEEK)), ))))

[sunny_talwar]

May be try to add Sum(Aggr()) around your dimension. Assuming your two dimensions from the chart are Week and Product, try this

Sum(Aggr(
  If(product_name = 'AD&D',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_add_flag),
  If(product_name = 'DBL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} canpol_week_testflag),
  If(product_name = 'PFL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_pfl_flag),
    Count({$<Year = {$(=Only(Year)-1)}>} gen_premium))))
, Week, product_name))

[mja_jami]

Hi Sunny,

Thank you very much for your reply and help. It’s a huge progress. I’m not sure if there’s anything wrong with this part below as few products are not showing, please see the picture.

If(product_name = 'PFL',

    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_pfl_flag),

    Count({$<Year = {$(=Only(Year)-1)}>} gen_premium))

Thank you very much. I feel I’m very close and cant see easy mistake. Would you please take a look?

Thanks & regards,

Jami

[sunny_talwar · Accepted solution]

How about if you add the set analysis to outer Sum expression as well

Sum({$<Year = {$(=Only(Year)-1)}>} Aggr(
  If(product_name = 'AD&D',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_add_flag),
  If(product_name = 'DBL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} canpol_week_testflag),
  If(product_name = 'PFL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_pfl_flag),
    Count({$<Year = {$(=Only(Year)-1)}>} gen_premium))))
, Week, product_name))

or even this

Sum({$<Year = {$(=Only(Year)-1)}>} Aggr(
  If(Only({$<Year = {$(=Only(Year)-1)}>} product_name) = 'AD&D',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_add_flag),
  If(Only({$<Year = {$(=Only(Year)-1)}>} product_name) = 'DBL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} canpol_week_testflag),
  If(Only({$<Year = {$(=Only(Year)-1)}>} product_name) = 'PFL',
    Count({$<Year = {$(=Only(Year)-1)}>} DISTINCT %PolicyKey) - Sum({$<Year = {$(=Only(Year)-1)}>} can_pfl_flag),
    Count({$<Year = {$(=Only(Year)-1)}>} gen_premium))))
, Week, product_name))

[mja_jami]

WOW! GREAT! I mean wow! THANK YOU SO MUCH. You're awesome!