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.
