- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
My Qlik shows:
Thank you very much for your help and effort.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)), ))))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content