topic Re: Help Creating Specifically Formatted Table Primarily With Calculations in QlikView

Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Sat, 12 Oct 2019 22:23:40 GMT

Hello:

I have an ask to create a table.

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

Re: Help Creating Specifically Formatted Table Primarily With Calculations

sunny_talwar — Mon, 29 Jan 2018 20:31:36 GMT

May be like this

Pick(Match(Product1, 'VSI (VM)', 'PSI (PSI)', 'SAN (TB)', 'NAS (TB)'),

Count({<[Approval Complete Year] = {'2018'},[Product Build End Type] = {'VSI OS Build Only'}>} [Host Name]),

Count({<[Approval Complete Year] = {'2018'},[Product Build End Type] = {'PSI OS Build Only'}>} [Host Name]),

Sum(({<ORDER_SYSTEM = {'PRIME'}, COMPLETED = {'', '%2018%'}, [SAN ACTION] = {'Add'}>} [SAN QUANTITY]),

Sum(({<ORDER_SYSTEM = {'PRIME'}, COMPLETED = {'', '%2018%'}, [NAS ACTION] = {'Add'}>} [NAS QUANTITY]))

Re: Help Creating Specifically Formatted Table Primarily With Calculations

sunny_talwar — Mon, 29 Jan 2018 20:33:59 GMT

The first column obviously does not have a column name. As part of an Inline Load, I can call it Product1 and then when displaying the table, simply use a space as the label name unless someone see's a potential issue in doing that.

Don't see any issues...

2018 Remaining (from Budget) - This is the 2018 Budget Volume Forecast column minus the 2018 Ordered to Date column

2018 Remaining (from Revised Forecast) - This is the 2018 Revised Forecast column minus the 2018 Ordered to Date column

This should be as simple as using Column references

1) Column(1) - Column(3)

2) Column(2) - Column(3)

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Sat, 12 Oct 2019 22:24:34 GMT

It doesn't like the next to last Sum.

Understood if you don't have time to look at it now. I'll see if I can get it right. Thank you so much for your help.

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Sat, 12 Oct 2019 22:25:36 GMT

For the Sum SAN line, it looks like I needed to add another ) at the end of it. For the Pick line, it also looks like I needed to add another ) at the very end of the whole expression.

Note how the last Sum is in black text and not blue. Lastly, if I remove the two Sum expressions and add a ) after Host Name for the last Count expression, it works and I get the correct VSI and PSI counts.

Re: Help Creating Specifically Formatted Table Primarily With Calculations

sunny_talwar — Tue, 30 Jan 2018 12:34:15 GMT

Try with this change (Remove one of the two parenthesis after Sum in the last two rows

Pick(Match(Product1, 'VSI (VM)', 'PSI (PSI)', 'SAN (TB)', 'NAS (TB)'),

Count({<[Approval Complete Year] = {'2018'},[Product Build End Type] = {'VSI OS Build Only'}>} [Host Name]),

Count({<[Approval Complete Year] = {'2018'},[Product Build End Type] = {'PSI OS Build Only'}>} [Host Name]),

Sum({<ORDER_SYSTEM = {'PRIME'}, COMPLETED = {'', '%2018%'}, [SAN ACTION] = {'Add'}>} [SAN QUANTITY]),

Sum({<ORDER_SYSTEM = {'PRIME'}, COMPLETED = {'', '%2018%'}, [NAS ACTION] = {'Add'}>} [NAS QUANTITY]))

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Sat, 12 Oct 2019 22:26:19 GMT

Hello Sunny:

I made the change you suggested by copying and pasting. One thing, and this is my fault, SAN ACTION and SAN QUANTITY should both have an _ between the words, i.e. SAN_ACTION. Same for NAS. I corrected my mistake on the field names and applied the change. I do not get any results for SAN or NAS.

So, I removed the COMPLETED = {'', '%2018%'} piece from both the SAN and NAS calculations and I then get results for both, but they are not correct. What I'm trying to achieve with COMPLETED = {'', '%2018%'} is if COMPLETED is Null or Empty or COMPLETED date is in 2018, then calculate. Make sense? So, using SAN as an example, If ORDER_SYTEM = "PRIME' and COMPLETED is Null or Empty or Otherwise Blank and the Year is 2018 and the SAN_ACTION = 'ADD', then sum the SAN_QUANTITY.

I've attached some sample data which may help. It's pre-filtered for SAN. I obviously have something wrong with the COMPLETED piece. Also, just in case, '' is two single quotes.

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Tue, 30 Jan 2018 14:38:50 GMT

Cool. Thanks.

Re: Help Creating Specifically Formatted Table Primarily With Calculations

sunny_talwar — Tue, 30 Jan 2018 14:46:22 GMT

Checking....

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Wed, 31 Jan 2018 04:35:26 GMT

Any luck? I've had minimal. I tried changing %2018% to *2018 and it appears to give me the right NAS number but not the right SAN number.

Re: Help Creating Specifically Formatted Table Primarily With Calculations

pnn44794 — Fri, 02 Feb 2018 23:27:05 GMT

Hello Sunny:

Sorry for my delay in responding or providing any additional info. I've been sick. Darn flu got me. Anyway.....

I'm marking your last pick(match response as the correct answer. It does indeed work as expected other than the SAN amount I get, but that's because of my data model. I switched a couple of fields to a different table and while I'm still not getting exactly what I'm expecting, I do get what that table has for a sum. I'll work on the data model separately.

As always, thank you very much for your help. I greatly appreciate it as always.