topic Constant Variables in QlikView

Constant Variables

EGilbert — Tue, 26 Nov 2019 14:40:01 GMT

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

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

Re: Constant Variables

gavinlaird — Fri, 29 Nov 2019 23:56:56 GMT

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.

Re: Constant Variables

EGilbert — Mon, 02 Dec 2019 15:59:38 GMT

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

Re: Constant Variables

EGilbert — Tue, 03 Dec 2019 17:55:23 GMT

Let me ask this a different way.

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?

Re: Constant Variables

gavinlaird — Thu, 05 Dec 2019 16:50:11 GMT

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

Re: Constant Variables

EGilbert — Thu, 05 Dec 2019 16:54:25 GMT

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

Re: Constant Variables

gavinlaird — Thu, 05 Dec 2019 23:52:14 GMT

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.

Re: Constant Variables

rwunderlich — Fri, 06 Dec 2019 02:28:15 GMT

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

{PlantId=P(ProductId), ProductId=}

-Rob

Re: Constant Variables

Brett_Bleess — Mon, 09 Dec 2019 22:11:48 GMT

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