Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I have an ask to create a table.
As always, any and all help is appreciated. Thanks in advance.
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]))
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]))
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)
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.
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.
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]))
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.
Cool. Thanks.
Checking....
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.