Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more 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   

chrisg
Contributor II

many thx! - well done

Not applicable

Rupinder, you are an absolute legend!

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

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

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?

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!

hector_munoz_in
Valued Contributor

Brilliant trick!!!

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