Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mja_jami
New Contributor III

Subtotal/ Sub on Pivot with nested If condition

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

Excel total.PNGExcelMy Qlik shows:Qlik Total.PNGQlik Total

Thank you very much for your help and effort.

 

1 Solution

Accepted Solutions

Re: Subtotal/ Sub on Pivot with nested If condition

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))
5 Replies
zhadrakas
Valued Contributor

Re: Subtotal/ Sub on Pivot with nested If condition

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

Re: Subtotal/ Sub on Pivot with nested If condition

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
New Contributor III

Re: Subtotal/ Sub on Pivot with nested If condition

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

PivotSubTot.PNG

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

Thanks & regards,

Jami

Re: Subtotal/ Sub on Pivot with nested If condition

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
New Contributor III

Re: Subtotal/ Sub on Pivot with nested If condition

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