topic Re: How to select multiple field values based on a single selection from the same field in Pivot tab in QlikView

How to select multiple field values based on a single selection from the same field in Pivot table

safik003 — Sat, 16 Nov 2024 05:15:01 GMT

I have a table which contains the fields "Produt No", "Product Name" , "Sales Date", "Warranty Period". I have created a pivot table with all the fields. A file is uploaded for details. These are my data:

Sales:

LOAD * INLINE [
Produt No, Product Name, Sales Date, Warranty Period
1, Apple Phone, 2/1/2018, 1 yr
2, Apple iPad, 5/12/2017, 1 yr
3, Apple Laptop, 6/7/2018, 2 yr
4, HP Laptop, 6/10/2016, 1 yr
5, Samsung Phone, 7/2/2017, 1 yr
6, Samsung TV, 5/4/2018, 2 yr
7, Samsung Laptop, 3/1/2017, 3 yr
8, Lenovo Lap, 1/2/2016, 1 yr
9, Lenovo Phone, 5/6/2018, 3 yr
10, Nokia, 5/5/2017, 2 yr
];

Data are shown in Pivot table looks like below. I have a filter "Product No", my question is if a user wants to select a value from Product No, three values(Previous value, Selected Value, Next Value) will be shown in the pivot table say if the user select value "4" from Product No, data for Product No 3,4 and 5 will be shown in the pivot table like below:

Produt No 3 4 5

Product Name Apple Laptop HP Laptop Samsung Phone

Sales Date 6/7/2018 6/10/2016 7/2/2017

Warranty Period 2 yr 1 yr 1 yr

Is there any way to do it? Need help urgently. Thanks a lot in advance.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Wed, 14 Nov 2018 19:51:44 GMT

Change your expression for Blank to this

Concat({<[Produt No] = {"$(=Max([Produt No]))", "$(=Max([Produt No])-1)", "$(=Max([Produt No])+1)"}>}'')

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Wed, 14 Nov 2018 20:38:55 GMT

It's great. Really appreciate your effort. But whenever I try to select any value from different list box. The values in pivot table is getting restricted i.e instead of showing three values, it's showing the selected values. Isn't there anyway to select the value from multiple list boxes and still three values will be shown in the pivot table? Check the attached file for your reference. Thanks

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Wed, 14 Nov 2018 20:42:08 GMT

Try that in that case

Concat({1<[Produt No] = {"$(=Max([Produt No]))", "$(=Max([Produt No])-1)", "$(=Max([Produt No])+1)"}>}'')

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Wed, 14 Nov 2018 20:50:23 GMT

Many thanks. Now it's working perfectly.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Wed, 14 Nov 2018 20:54:39 GMT

Awesome!!

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Wed, 14 Nov 2018 21:03:51 GMT

One more help is needed. How can I highlight the changes value in pivot table? For example,for product no "3" the product name is apple but for product no "4" it's Samsung. How can I highlight this change?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Thu, 15 Nov 2018 00:45:40 GMT

Highlight how? I am not sure how you want to show it in your chart?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Thu, 15 Nov 2018 06:12:56 GMT

User wants that feature.in max cases the product name remains same,if suddenly it changes in the pivot table,we need to highlight this. Isn't there any expression for it? If the value changes we will put a color on it. Isn't it possible?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Thu, 15 Nov 2018 14:08:53 GMT

But in your example.... the Product Name always change with Produt No and it sort of makes sense (because each Product Name is associated with a different Produt No).... then will you always see everything highlighted?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Thu, 15 Nov 2018 17:18:52 GMT

Yes.Correct. You are absolutely right, in my example product name changes with every product no but in some cases the product name changes rarely, in that case I need to highlight the change value.Attached the file where product name rarely changes, how can I highlight this?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Thu, 15 Nov 2018 17:36:54 GMT

Something like this?

Expression used for background color for the expression

If(Only({<[Produt No] = {"$(=Max([Produt No]))", "$(=Max([Produt No])-1)", "$(=Max([Produt No])+1)"}>}[Product Name])
<>
Before(TOTAL Only({<[Produt No] = {"$(=Max([Produt No]))", "$(=Max([Produt No])-1)", "$(=Max([Produt No])+1)"}>}[Product Name]))
and ColumnNo(TOTAL) > 1, Red())

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Thu, 15 Nov 2018 17:46:19 GMT

You are almost there but instead of highlighting the expression total, Can't I just highlight the product name i.e the change product name "Samsung Phone"? Hope you got my point.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Thu, 15 Nov 2018 17:54:37 GMT

Use the same expression in dimension's background color expression 🙂

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Thu, 15 Nov 2018 17:57:44 GMT

Genius, you are. Many thanks.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Mon, 19 Nov 2018 18:21:20 GMT

Hi,

Whenever the product name is changing from Apple to Samsung or other value. The color feature is not working. Can you please make it more dynamic? It needs to highlight the change of values of "Product Name" dimension. Thanks.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Mon, 19 Nov 2018 18:27:22 GMT

Make it more dynamic? I am not sure I understand what isn't changing colors? Can you attach a sample with some images showing the issue from the sample?

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Mon, 19 Nov 2018 18:36:43 GMT

Yes. whenever I try to implement the expression in my actual dashboard. Sometimes it's giving unusual output like if product name changes from "Apple" to "Apple then " Samsung" it's highlighting the "Apple", not "Samsung". I want highlight the value after the change value of Product Name.Hope you got it

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

safik003 — Mon, 19 Nov 2018 18:38:43 GMT

One point to add. when the value isn't changing like Product Name is always "Apple" then also it's highlighting one "Apple" Value.

Re: How to select multiple field values based on a single selection from the same field in Pivot tab

sunny_talwar — Mon, 19 Nov 2018 18:42:57 GMT

I won't know until I see it.