*** Always show one row in Pivot Table ***

[IAMDV]

Dear All,

I need some help on what might seem to be a simple problem but I can't get my head around. I have mocked an example for you understanding.

Problem Description:

I have a Fact table with 11 rows and showing them in a Pivot Table.I wanted to ALWAYS show one row Irrespective of what Dimension you select. I.e. I have Dimension values from Dim 1 to Dim 10 in my Dimension table but I don't have - "Dim Always Show" in my Dimension table. So how do I always show/retain this row while users are making selections? Ideally I wanted to solve this problem in script (If possible, please!).

Expected Results:

To summarize, if I select Dim 3 for example, then I wanted to retain Dim 3 and Dim Always Show rows in my pivot table. I hope this makes sense. I'm attaching the excel file for your reference.

Thanks in advance.

Cheers,

DV

[swuehl]

Hm, if I correct the missing field name in your fact table, it doesn't look too bad to me.

Could you point me to what you are missing in that sample? What do you exactely expect to see when

making selections?

[IAMDV]

Hi Stefan,

Please accept my apology if I wasn't clear while you're helping me. Please see my latest attachment and I have created following objects:

1. Final output table (cross tabled in script and using EAV model for dynamic column selections)

2. Col Selection listbox

3. Dim 01, Dim 02 & Dim 03

Now, if you make selections on Col Selection listbox then you are filtering rows, however I have pivoted my Attribute hence you are looking them as columns. And now I see the "Don't Filter" row no matter what I select but the problem is that I lost the power of associations. I.e when I select Dim 3 then I see all the values in Dim 1 & Dim 2 vice-versa instead of just seeing the possible value(s). How do I solve this problem without losing the "Don't Filter" row in the Final Output Table.

Ps: The reason I'm using "Don't Filter" row because when we Cross Table in QlikView. We will lose the NULL values, which is perfect for very large data sets because we don't storage in rows....but in turn we are also missing those columns in the Final Output Table as they are stored as rows.

I hope this makes sense. Please shout if I'm not clear.

Many thanks again for your support.

Cheers,

DV

[whiteline]

Hi DV.

Played with your example for a while...

Why don't you use the 'total' row for that purpose ?

You could change the lable and then disable bold and make the background white using 'custom format cell' in advanced styling mode.

Finally you should use an expression like this:

=if(Dimensionality()<>0, Only(Data), 'Don''t filter')

The big advantage is that the user can't select totals, while in your example he can. And this will broke everithing. The attempts to solve this can lead you to even more complex solutions with field triggers and so on.

Of course, it only makes sense, if you're not supposed to use totals in your final table (as you can't define different labels for each level).

PS: Another trick that usually helps is to use two or more separate overlapping objects that look like one.

[IAMDV]

Whiteline - Very clever! I like the technique of using "Total" row. I'd have used this technique if I'm dealing with smaller data set. But unfortunately I'm dealing with massive data set and it's around 500+ million rows. So using "IF" statement will completely slow down the performance. It's like Table Scan in SQL instead of Index Seek. But I really appreciate your time & effort, thank you mate.

I'm using one-to-one relationship between my Dimension & Expression fields and not performing any aggregation (i.e. just using Only() function). And pivoting them as columns for faster column selections. So users think they are making column selections but in turn they are just filtering the rows. This approach works great except the NULL rows will go missing for current selection and that means I won't see all the NULL valued columns. Hence I'm using this "Don't Filter" row as concatenated load in my Fact table and Dimension Tables to make sure users always see atleast one value pertaining to their selection.

I hope Stefan can do some magic!?

Cheers,

DV

[whiteline]

Ah, so the goal is just to show all values, including nulls ?

[IAMDV]

That's correct. I just wanted to show all the values but I have removed those Null values in the process of using cross table. That's where I'm reducing the data volume. One last step away but some times the last logic step determines the whole effort.

Cheers,

DV

[Gysbert_Wassenaar]

I don't see any way around this that doesn't involve either substituting all null values with real values or using an expression like only({$+1<Dimension_Field={'Dim Show Always'}>}Data) or a somewhat ugly Show All Values for the Attribute dimension + disabling Suppress Zero-values.

[IAMDV]

Many thanks Gysbert. I agree with you...it looks like I've hit the brick wall on this problem. However, I've a quick question on Set Analysis approach.

only({$+1<Dimension_Field={'Dim Show Always'}>}Data)

Can we write the set expression where it respects "Attribute" field in the above example. I mean let's say if I select Measure_01 & Measure_02 then Set expression should hide/exclude Measure_03. I think we can use Set Operators to get this working. Any thoughts?

Thanks,

DV

[IAMDV]

Ignore me guys! I've got the Set Analysis Syntax. Many thanks again for all your support. I wish I could solve this one without Set Analysis i.e. without any additional CPU cycles. I'll update you guys if I come with a way of solving this one within script.

Thanks to Stefan, Whiteline & Gysbert.

Cheers,

DV