Totals in Dimensions regardless of selection

[eigorman]

Hey all. I am sure this is an easy one. But wanted to get it straight.

I have a bunch of data that is based off of two dimensions that I care about. ICTO and LOB. ICTO is the overall organization and the LOBs are the lines of businesses. There are other layers but not of a concern here. When I select a LOB data, I still want to be able to show the ICTO which is the overall as the overall total, and not take the selection. How is this done?

Ed

[sunny_talwar]

May be ignore selection in LOB field?

{<LOB>}

[eigorman]

Not sure - when I tried to update the dimension did not seem to work

[sunny_talwar]

Use that in your expression

Assuming you have Sum(measure), try doing it like this: Sum({<LOB>} measure)

[eigorman]

I have  three dimensions. ICTO - LOB and TGO

I have a bunch of expressions that do calculations . some counts/ some rag ratings and a percentage. There is more of the same to the right. In this chart you see CCB which is really the only entry in that field and it is showing the selected total, instead of the overall information. So I want to be able to select a LOB (Pass Rates) and still show the overall CCB totals/ percentages

[sunny_talwar]

What are you expressions? {<LOB>} in your expression should have helped... not sure what isn't working.

[eigorman]

I have multiple

=Sum(LPCount)/Count([LPAppCount]) which is the rag and the lc expressioin. App is a count, count(ID)

[sunny_talwar]

So, does this solve your problem?

=Sum({<LOB>}LPCount)/Count( {<LOB>}[LPAppCount])

or this

=Sum({<LOB>}LPCount)/Count( {<LOB>}[LPAppCount]) * Avg(1)

[eigorman]

No - all this does is show the other LOBs which I didn't want to see. I just want the Dimension ICTO not to use the current selection so that it shows the overall total for it, and not the total for the selected as you saw above. So the app total for CCB should be 316 not 12 as it listed above.

Ed

[rwunderlich · Accepted solution]

In your expressions, use set analysis to ignore LOB selections only on the ICCTO total row. You can test which row using Dimensionality(). For example, consider a pivot table with two dims, Year, Product. The expression to calculate sales would be:

if(Dimensionality()=1

,sum({<Product=>}Sales)

,sum(Sales)

)

Sample attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com