Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
mja_jami
Contributor III
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

ExcelExcelMy Qlik shows:Qlik TotalQlik Total

Thank you very much for your help and effort.

 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
zhadrakas
Specialist II
Specialist II

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

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

sunny_talwar

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

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