Roll Up Sum By CIO and Pivot Table Format Help

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

Hello:

I have a need to sum compute quantities and storage quantities, by CIO for the year 2017, as volume and the associated dollars. I've attached sample data and below is the desired output format (guessing the formatted output has to be a Qlikview pivot table).

The calculations should be for Approval_Year = 2017 (would be nice to limit the table to just rows for Approval_Year 2017 - I do have a variable for current year LET vCurrentYear = Year(Today()); ).

The calculations should be as follows:

For Compute Grow, if there is a value in the Bespoke, PSI, VSI, VSI-D or VSI-P columns and effective_item_action is Add, then effective_item-qty should be added together and summed up by CIO as Volume. The same is then true for adding together and summing up effective_year1_prorated_cost for the $$$ column below.

For Storage Grow, if there is a value in the Backup, NAS or SAN columns and effective_item_action is Add, then effective_item-qty should be added together and summed up by CIO as Volume. The same is then true for adding together and summing up effective_year1_prorated_cost for the $$$ column below.

Conversely, for Compute Shrink and Storage Shrink, use and effective_item_action = Remove.

The format I've been asked to produce is below the inline load info. Please note that GROW, SHRINK, COMPUTE GROW, STORAGE GROW, COMPUTE SHRINK and STORAGE SHRINK are a very simple inline load as follows. All other data is retrieved via SQL queries to SQL Server.

[Titles]:
LOAD * Inline
[
Table_TitleG, Table_TitleS, Sub_Table_TitleGC, Sub_Table_TitleGS, Sub_Table_TitleSC, Sub_Table_TitleSS
GROW, SHRINK, "COMPUTE GROW", "STORAGE GROW", "COMPUTE SHRINK", "STORAGE SHRINK"
];

GROW	SHRINK
COMPUTE GROW	STORAGE GROW	COMPUTE SHRINK	STORAGE SHRINK
Volume	$$	Volume	$$	Volume	$$	Volume	$$
Angel
Castro
Tiller
Yancy
Zeller

As always, any and all help is appreciated. Thanks in advance.

Re: Roll Up Sum By CIO and Pivot Table Format Help

pnn44794 — Sun, 08 Oct 2017 19:12:16 GMT

Closing discussion out. I started a different, but related thread.

topic Roll Up Sum By CIO and Pivot Table Format Help in QlikView

Roll Up Sum By CIO and Pivot Table Format Help

Re: Roll Up Sum By CIO and Pivot Table Format Help