Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Help Creating Specifically Formatted Table Primarily With Calculations

Hello:

 

I have an ask to create a table.

 

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

1 Solution

Accepted Solutions
Highlighted

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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

View solution in original post

10 Replies
Highlighted

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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

Highlighted

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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)

Highlighted
Partner
Partner

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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.

 

Highlighted
Partner
Partner

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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.

Highlighted

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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

View solution in original post

Highlighted
Partner
Partner

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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.

Highlighted
Partner
Partner

Re: Help Creating Specifically Formatted Table Primarily With Calculations

Cool.  Thanks.

Highlighted

Re: Help Creating Specifically Formatted Table Primarily With Calculations

Checking....

Highlighted
Partner
Partner

Re: Help Creating Specifically Formatted Table Primarily With Calculations

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.