topic Re: Forced Selection in Pivot Table in QlikView

Forced Selection in Pivot Table

marleygt — Wed, 04 Nov 2015 12:05:38 GMT

Hi all!

Here I am again with another tricky question.

I would like to know if is possible in a list box, to have a column which is not considering Dimension limit or that I can force to make a total sum of some specific fields;

As is not easy to say in words, herewith you can find a sample which will be more clear to understand.

Inside you'll find a Pivot Table where my Dimensions are 'Company' and 'Month';

My Request is: to have total (by Month) for prev Year and Curr Year, plus, a colum where I can see total amount in case user select more then 1 month.

For instance: if I select

Jan + Feb + Mar

- 2014

- Alpha

I would like to see 38 (Jan 10 + Feb 15 + Mar 13)

Is there a possibility to do that in the same List box?

Thank you for your help or any suggestions!

Alex

Re: Forced Selection in List Box

Gysbert_Wassenaar — Wed, 04 Nov 2015 12:52:19 GMT

See attached qvw. Is that what you want?

Re: Forced Selection in List Box

dmac1971 — Wed, 04 Nov 2015 12:59:10 GMT

Just add Month-Year as a list box. Then click Alpha, then Jan-2014 to Mar-2014?

You can use CTRL to select more than one option in a list box, ie Hold Down CTRL and then click the filters you want.

Re: Forced Selection in List Box

marleygt — Wed, 04 Nov 2015 13:45:19 GMT

Thank you gwassenaar‌ and dmac1971‌!

First Solution its amazing and I will keep in mind (or eventually I'll set up something if there is no way to do it in the same chart)

[I just realize that I wrote "Is there a possibility to do that in the same List box?" and it was my mistake cause I mean Is there a possibility to do that in the same Pivot Table?" - Sorry ]

So, solution is ok but what I need is to have exactly your solution but here:

dmac1971‌

I've tryed out your idea, but is not working properly:

Thank you anyway!

Alex

Re: Forced Selection in List Box

dmac1971 — Wed, 04 Nov 2015 14:00:59 GMT

It wont work as is because you have chosen LED format for the month list box. Change it back to default and it works perfectly.

Re: Forced Selection in Pivot Table

marleygt — Wed, 04 Nov 2015 14:07:22 GMT

dmac1971‌ probably I am doing something wrong...

....but what?

Changed to default, bus still having same result; did you changed something in the expression?

Re: Forced Selection in Pivot Table

dmac1971 — Wed, 04 Nov 2015 16:34:54 GMT

Hi Alex,

Remove the Month-Year ListBox, as you no longer have the LED type on the Month List box you wont need them.

Then clicking on Alpha, 2014 and then Click on JAN, keep mouse button down and also select Feb and March should give you what you need.

Dermot.

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 08:29:13 GMT

dmac1971‌, I think we are talking about different thing;

with a second better look on the image you've posted, (I didn't enlarge before), I realize that even in your way, the "Actual selection (total Month), is not working properly; it's still showing "10" as Total of 3 Months selected, but is not correct.

Right one is what I've already shown in the text box (38).

At this point probably I think I have to change completely the meaning of Pivot Table, maybe with a separate ListBox showing Grand Total of Selection.

If there are any other suggestion, they're welcome...otherwise, thank you for your patience and collaboration!

Alex

Re: Forced Selection in Pivot Table

dmac1971 — Thu, 05 Nov 2015 08:35:43 GMT

Alex here's your original file with the month list box changed to a normal format and the three months selected. The result is 38 and your text box appears, isn't this what you wanted to achieve? Apologies if I've picked it up wrong!

Have added straight table to show results, and a dynamic heading showing the current year selected.

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 10:02:39 GMT

Thank dmac1971‌!

I see that with the Straight Table is almost working but, still have some problem.

Let me show you something:

This is the situation for what I am asking solution.

I can't move "Month" dimension on left side, cause I have multiple data focuses;

This is the best visualization I can use in this moment due to spaces in the sheet (all spaces already filled up).

Switching on left "Month" means having a never ending scroll down, cause for any Company/Products/etc I have a division of month, value 2014, value 2015 toooooooo much long and not easy to use as rapid sight for main important data.

So, that's why I've requested if possible to have the first column with a kind of forced expression who doesn't take care of Month Dimension, but consider my actual selection.

I think there's no solution on this way.

Probably I must change completely the format/type of chart...even changin in ListBox maybe.

I tryout some combination now, with your suggestions and the first Idea of gwassenaar‌..maybe I can put a Listbox overlaying a part of Pivot Table...or something like that.

However, thank you very much for your help!

Alex

Re: Forced Selection in Pivot Table

Gysbert_Wassenaar — Thu, 05 Nov 2015 10:14:11 GMT

Do you mean like this?

Re: Forced Selection in Pivot Table

dmac1971 — Thu, 05 Nov 2015 10:32:36 GMT

Alex there is always a solution, could you show us exactly what you want in excel, even if you just mock up the table?

You could of course use containers on the sheet, allowing multiple different views in the same page area?

I've added another straight table that might do what you are looking for. The headings are dynamic and change depending on the month selection, however when lots of months are selected it changes from showing the actual months selected to showing those not actually selected. There may be a way around this I'm not sure.

Keep at it nearly there

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 11:46:04 GMT

Ok! With your last (both) reply I think I should be able to solve!

I miss just one thing that I am sure you know how to handle easily.

Using gwassenaar‌ solution, is working right for my purpose but, need to understand how to show right data here:

If I select Jan and one Company:

Then Feb and same Company:

Jan+Feb Selection:

So, Total is ok! GREAT! 😄

New Problem:

and even If I take several selection:

It gives me absolute total of all Month (or selected Months) and equal result for all Companies. That's is not good.

Is possible to have right result in this case?

If there is a way also for this, I think goal will be reached completely!

Alex

PS: dmac1971‌ if you still need the excel - I can prepare and upload, but solution is closer...

Re: Forced Selection in Pivot Table

Gysbert_Wassenaar — Thu, 05 Nov 2015 11:50:53 GMT

That looks like you're using sum(total Something) instead of sum(total <Company> Something) or perhaps in your case sum(total <Azienda> Something). Replace the Something with whatever field you want to sum.

Re: Forced Selection in Pivot Table

dmac1971 — Thu, 05 Nov 2015 11:51:43 GMT

How many years data do you have in your model? If you have more than 2014 and 2015 I suspect the 603 total is coming from previous years as well?

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 13:19:35 GMT

This is the Expr;

I've just added "Total <DimensioneCategory>" as you suggested - which is a multiple field (Company,Product,Type,Brand...etc);

If I select for instance Brand,

then data will be:

I mean, I can't use just one Single Field...but should it work even with multiple fields I suppose.

What I am doing wrong?

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 13:22:24 GMT

4 Years is calendar capability, but data are filled up with 3 at the moment.

Re: Forced Selection in Pivot Table

dmac1971 — Thu, 05 Nov 2015 13:28:22 GMT

So where it says Valore above, translated to Value I think, what did you expect to see here instead of 42,976? Total for the four years for this account, or for 2 years etc etc. As Gysbert stated you are adding up everyone for everything I think?

Re: Forced Selection in Pivot Table

marleygt — Thu, 05 Nov 2015 14:11:09 GMT

Perfect solution might be:

No Selection (total of the year - in this case, selected of course 2014 or 2015..etc)

(In this case without selecting a Company name - I've selected for show right result I wish to see without selection)

When I select - let say - Jan, 2014, then the same data in corresponding year slot

(Firs column in this case will be the same of Volume 2014 - That's right)

And....finally here we are at the real reason of this post:

If I select Jan + Feb, I want to see sum of both month (771 - result of Jan 395 and Feb 375 [with decimals not displayed]).

So Need to see without no selection (exception made for the year as I said before) , total of the year, but not the Grand Total of all years and much more important, not the same result for all the different Companies (that makes non-sense), but right result for all companies.

As words are always more complicated, here:

Alex

Re: Forced Selection in Pivot Table

marleygt — Fri, 06 Nov 2015 09:23:26 GMT

dmac1971‌ gwassenaar‌

Morning Guys!

Just to let you know that with few further checks, everything is now working perfectly!

Thank very much for sharing your time, ideas and suggestions!

Have a nice day!

Alex