Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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!