Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
EGilbert
Contributor III
Contributor III

Constant Variables

I have a data set similar to the one below but much more complex.

Sample Data Set 2.JPG

I would like to show a chart similar to what is shown but have a list box where i select the product and see only plants that make that product.  So if I select 'product D' I only see Plants 2 & 4.  That I can do but I would also like to see the Open Cap at each of those plants in addition.  When i select the product i want the open cap does not get shown.  Ideally I would see all product at those plants but Open Cap is the one i need for sure.  Essentially i want to use product name to narrow plants shown but show all products at those plants.

 

Thanks 

8 Replies
gavinlaird
Contributor III
Contributor III

If your expression previously was "sum(Amount)", you could change it to "sum({<Product-={'Open Cap'}>} Amount) + if(sum(Amount)>0, sum({1<Product={'Open Cap'}>})"

The first half of the expression sums up all values for all products except Open Cap. The second half ignores your selection of a specific product, and adds in the value of the Open Cap product at that Plant. The if statement is required so you aren't displaying Open Cap for the Plants that do not make the selected product.

EGilbert
Contributor III
Contributor III
Author

Thanks for the help.  I cannot get the formula you provided to work.  I add the first part to a simple sum expression and I see basically what I want but to your point I see every plant whether  they provide the chosen product/architecture I select in my list.  I cannot get your 'if' statement to work but when I modify it I either get nothing or just the selected demand.

Here is what I have to get all plants open and applicable plants selected architectures

sum(DISTINCT [Demand A])+Sum({<[Architecture A]={'Open'}>} [Demand A])

Thanks Again

Ed

 

EGilbert
Contributor III
Contributor III
Author

Let me ask this a different way.  

Sample Data Set 2.JPG

In the above example how would I use a listbox to select the product ID and have the chart show just the plants that make that product, but show the whole plant look so all products and open capacity.  In essence use the product ID in the listbox as a filter for what plant dimension values to display, but not impact the product detail data in the sum expression?

gavinlaird
Contributor III
Contributor III

You need the 1 in the set analysis of the second statement to make it ignore the user's selection:

sum(DISTINCT [Demand A])+Sum({1<[Architecture A]={'Open'}>} [Demand A])

EGilbert
Contributor III
Contributor III
Author

Thanks for the response.  I added the 1 and the open bars do show up, but it also adds the open for every plant, not just those where the architecture selected in the list box are produced?  That is key to make the chart usable.

 

Thanks again

gavinlaird
Contributor III
Contributor III

Sorry! Please try the expression below, which now checks if the plant produces the selected architecture before adding the open cap.

sum({<[Architecture A]-={'Open'}>} DISTINCT [Demand A])+if(sum(DISTINCT [Demand A])>0,Sum({1<[Architecture A]={'Open'}>} [Demand A]))

Also, make sure you include [Architecture A]-={'Open'}  in the first part of the expression, so you don't count the open cap twice. 

rwunderlich

Whatever expressions you are using to create the bars should include the set:

{PlantId=P(ProductId), ProductId=}

-Rob

Brett_Bleess
Support (Former)
Support (Former)

Did either of the last two posts get you what you needed?  If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that helped you get things working as you wanted.  This gives credit to the poster(s) for the assistance and tells other Community Members what worked, so please be sure you return to do this step.  If you are still working on things, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.