How To Roll Up A SUM Based On Multiple Fields Not Being Null

[pnn44794]

Hello:

I have a need to sum quantities and dollar amounts based on certain fields not being null / empty.  The sum amounts need to be grouped by GROW --> Compute Grow and Storage Grow --> CIO as in the sample table below.  I'm guessing the table needs to be a Qlikview Pivot Table though I'm open to suggestions or what will get the format close.

For the Compute Grow Volume (quantity) sum column and dollar amount sum column ($$$), the calculation should be based on the following:

• Approval_Year = 2017
• If the Bespoke, PSI, VSI, VSI-D and VSI-P have values for Approval_Year = 2017 and effective_item_action is Add, then they should be added together using the effective_item_qty value for the corresponding row (do not use the actual values in the Bespoke, PSI, VSI, etc columns) and summed up by CIO - The value should go into the Volume column per CIO
• For the dollar amount ($$$ column in sample table below), the column to use is the effective_year1_prorated_cost column for the corresponding row

I've attached sample data.  The following is the output format needed, if possible.

As always, any and all help is appreciated.

[pnn44794 · Accepted solution]

I believe I have this solved with help from Peter Cammaert suing the following expression:

=(215 - (mid(Approved_YearMonth, 3, 2)*12+right(Approved_YearMonth, 2))) * [Monthly Spend]