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

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

pnn44794 — Tue, 10 Oct 2017 20:50:14 GMT

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]

topic Re: How To Roll Up A SUM Based On Multiple Fields Not Being Null in QlikView

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

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