topic Re: Show all values in a dimension in QlikView

Show all values in a dimension

Fri, 10 May 2013 09:57:47 GMT

Hi All,

I have a requirement to show all countries by month in a pivot with month running horizontally and country vertically. When the user changes the month, the dimension country is reduced to show only countries that have a value and everything else is a null.

I have tried setting to not suppress nulls, but this doesnt work, i have tried to show all values but this does not work

If a create a listbox and write this expression concat(DISTINCT {1}country,',') then irrespective of selections I can see all countries. This expression does not give the same output as a dimension and actually populates nothing.

So how do i get the concat string function output in a listbox to work in a dmension on a pivot table

Your help is much appreciated

Kind Regards,

Byron

Re: Show all values in a dimension

Fri, 10 May 2013 10:01:36 GMT

just uncheck Supress Missing Values. in the presentation tab

Re: Show all values in a dimension

er_mohit — Fri, 10 May 2013 10:04:46 GMT

in presentation tab uncheck supress Zero value

hope it helps

Re: Show all values in a dimension

Fri, 10 May 2013 10:11:52 GMT

Thank you both for your replies. Tried the above as well and still no luck

Re: Show all values in a dimension

Fri, 10 May 2013 10:17:10 GMT

Use maximum columns available with u in the chart as dimensions and then check.

thts not going to help u out but atleast u will knw whts the difference its making on the expression part to get the country dimension to get eliminated.

Re: Show all values in a dimension

Fri, 10 May 2013 10:41:56 GMT

Thank you for your suggestion mate. Unfortunately, the model is not my own and actually helping a friend out so don't have much visibility as to what his data set looks like. If we figure it out, I will update this post for future reference.

Closing it now

Cheers,

Byron

Re: Show all values in a dimension

Gysbert_Wassenaar — Fri, 10 May 2013 17:55:53 GMT

See attached qvw.

Re: Show all values in a dimension

Sun, 12 May 2013 08:43:45 GMT

You are too good Gysbert !!!

Thank you very much!

Cheers,

Byron

Re: Show all values in a dimension

Thu, 21 Aug 2014 15:42:47 GMT

Hi Gysbert i'am with a similiar problem but in a chart can you take a look in my thread when possible?

Show Dimension and zeros even if no values

Thanks in advance.

Re: Show all values in a dimension

Fri, 13 Feb 2015 16:29:00 GMT

Very good - solved my problem - thanks

Re: Show all values in a dimension

Mon, 23 Feb 2015 13:18:08 GMT

Hi. This work for most solutions. But as soon you do apply filters to your set it can get absolutly weird.

When is this the case:

Attached example of a cross tab: dimensions used which have no intersection: There are customers without a transaction = they have no relation to the calendar
As long nothing is selected everything works fine. Why? because still all relations are in the selected set
As soon you select a year everything falls appart: Why? because by selecting a year you filterd out all customers from the current set. Even activating as proposed "show all values" for a dimension doesn't solve the problem as the relations to other dimensions are gone
Solution: take a set into your formula with contains the link between the dimensions.

Example:

sum(Ordervalue)+ sum({1-1<[Year]={"*"}>}0)

= First part is the original formula. The second part selects then all customers and subtracts all customers with a valid link to a year = show all customers with absolutly no revenue. There may be other requirements like: show as well all customers which had revenue but in other years. In such cases you would use getFieldsSelection() in the set definition as modifier.

Example is attached .

Re: Show all values in a dimension

dadumas — Mon, 21 Sep 2015 20:19:35 GMT

This seemed to work better for me:

anyexpression + sum({$<[Year]={"*"}>}0)

When I tried the 1-1 vs the $, the 1-1 produced very strange results. Using $ (current selection state) worked perfect.

Dave

Re: Show all values in a dimension

LR — Thu, 10 Jan 2019 19:57:15 GMT

I have a very similar question to this. But I am unable to open the qvw. file attached. I am trying to show all values in a field irrespective of my where clause. I am using Section Access for my security and need to show all Departments, for example, even if they have no data tied to them. Can you help with the load script?

Thanks!