topic Re: How to get Pivot to allow multiple selections in QlikView

How to get Pivot to allow multiple selections

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I have a pivot showing various counts by month. The expression is conditional and is based on the current selection in a listbox. Is there a way to allow multiple selections so that all selected counts will show? I dont want to show all counts at once because the table becomes too wide.

For example, I have GetFieldSelections(ShowBillCountType)='Transaction Count' and GetFieldSelections(ShowBillCountType)='Invoice Count'

If I select both fields then I receive an error message but users might want to compare some counts. It is inconvient to have to unselect Transaction Count and then select Invoice Count. I would even accept the option of only having one field selected but I do not want to use "Always One Selected Value" because I also have a button that toggles to amounts and then it will show counts with the amounts.

I tried something like:

GetFieldSelections(ShowBillCountType)='Transaction Count' or (GetFieldSelections(ShowBillCountType)='Transaction Count' and GetSelectedCount(ShowBillCountType)>=1) but this did not work either.

Re: How to get Pivot to allow multiple selections

sunny_talwar — Thu, 21 Dec 2017 17:59:07 GMT

Try this

SubStringCount('|' & GetFieldSelections(ShowBillCountType, '|', 100000) & '|', '|Transaction Count|')

SubStringCount('|' & GetFieldSelections(ShowBillCountType, '|', 100000) & '|', '|Invoice Count|')

Re: How to get Pivot to allow multiple selections

greend21 — Thu, 21 Dec 2017 18:24:22 GMT

Thank you! Do you think you could explain how this works?

Re: How to get Pivot to allow multiple selections

sunny_talwar — Thu, 21 Dec 2017 18:29:03 GMT

For multiple selections, you get a multiple values... for instance both Transaction and Invoice Count is selected... you will see something like this

Invoice Count|Transaction Count... now SubStringCount() checks how many times the give value returns

SubStringCount('|' & GetFieldSelections(ShowBillCountType, '|', 100000) & '|', '|Transaction Count|')

When both are selected, Transaction Count shows up once, the expression will equal 1 and the result will be seeing the column. When it is not selected, the value will be 0, and the column won't be seen

Re: How to get Pivot to allow multiple selections

greend21 — Thu, 28 Dec 2017 15:25:06 GMT

Can this work with set analysis to pick up selections in an alternate state? My table and list box are in the same alternate state. The listbox isnt actually linked to the table but the conditional show is allowing the expression to show depending on what is selected. Unfortunately, once put in the alternate state the selections are no longer working so I think I need this in the condition. The list box must be in the alternate state so it does not affect other tabs.

Re: How to get Pivot to allow multiple selections

sunny_talwar — Thu, 28 Dec 2017 15:38:32 GMT

Yes it can... the fourth parameter of GetFieldSelection is the State Name and I believe you can supply the state name within single quotes

Re: How to get Pivot to allow multiple selections

greend21 — Thu, 28 Dec 2017 15:57:17 GMT

Thanks! I was looking in the Substringcount instead. Any idea why my two listboxes that ARE part of the data set no longer work? If I change them to default state they do but then they affect the other tabs?

Re: How to get Pivot to allow multiple selections

sunny_talwar — Thu, 28 Dec 2017 15:59:40 GMT

I have no idea... can you share more details? What is the state name? What is the expression you tried? etc?

Re: How to get Pivot to allow multiple selections

greend21 — Thu, 28 Dec 2017 16:09:54 GMT

Nevermind. Syntax error on my part.