Help Creating Specifically Formatted Table Primarily With Calculations

[pnn44794]

Hello:

I have an ask to create a table.

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

[sunny_talwar]

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]))

[sunny_talwar]

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)

[pnn44794]

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.

[pnn44794]

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.

[sunny_talwar · Accepted solution]

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]))

[pnn44794]

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.

[pnn44794]

Cool.  Thanks.

[sunny_talwar]

Checking....

[pnn44794]

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.