Qlik Community

QlikView Documents

Documents for QlikView related information.

Show all values in Pivot/Straight Table (irrespective of current selection)

rupindo_barcap
New Contributor II

Show all values in Pivot/Straight Table (irrespective of current selection)

Hello,

There are numerous times I have come across the requirement to show all values of a Dimension in a Pivot or Straight table irrespective of any selection.

Default behaviour of Qlikview is to show only those values that have value with respect to the current selection.

There are times when "Show All Values" option (Show all values in a dimension) doesn't work.

One way of solving this could be create empty rows with all possible dimension value combination which could be a nightmare.

Below workaround can achieve the same with minimal effort.

    Original Expression - sum({your set analysis} Sales). Eg - sum({<Year = {"2014"} , Region = {"Americas"}>} Sales)

     Modified Expression - if (sum({Ignore All dimensions} Sales) <> sum(Sales), sum({Your set analysis} sales, 0)

                                   . Eg if (sum({1} Sales) <> sum(Sales), sum({<Year = {"2014"} , Region = {"Americas"}>} Sales), 0)

I think this makes Qlikview evaluate all the possible dimension values which have Sales (irrespective of any selection).

The result you get is the one you need as the if clause will always be TRUE(write it such that its always true) and will show 0 values for all the unrelated records.

Comments
danieloberbilli
Valued Contributor II

Great idea! Thanks a lot for sharing this!

Not applicable

Exactly what I was looking for!!  Thank you for sharing!

lubicz99
Valued Contributor

Rupinder, you are a genius.  This helped me big time today   

Partner
Partner

many thx! - well done

Not applicable

Rupinder, you are an absolute legend!

You are now the Qlik God, All Hail King Rupinder!

0 Likes
Not applicable

I have an issue.

With this code, it adds one line from 2012 to 2016

How can i delete it?

I just want the top line with data.

Thak you

0 Likes
joey_lutes
Contributor

Great idea - I love little tricks.  One thing:

My issue revolves around the same topic, however my expression doesn't contain set analyis (one of the few!)

so basically, I tried (eg)

if(sum{1}sales) <> sum(sales), sum sales)

no dice.  thoughts?

0 Likes
geertrops
New Contributor III

Awesome, thanks!

By the way, this:      if (sum({1} Sales) <> 999999999, sum(Sales), 0)

works better than:     if (sum({1} Sales) <> sum(Sales), sum(Sales), 0)


Good luck!

0 Likes
hector_munoz
Valued Contributor

Brilliant trick!!!

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-02-12 07:01 AM
Updated by: