Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_burgess
Contributor III
Contributor III

Calculated Dimension that Uses Complete Set

Hi.

I have a situation where I am trying to restrict the Dimensions in a straight table, and preferably by not restricting them via the expressions since these are quite complicated and involve a number of other dimensions.

To better explain I will try and simplify my question this way:

I have 5 company Plants: A, B, C, D and E.

Each Plant has Stock on hand and average weekly Sales, so the expressions (remember, very simplified) are sum(Stock), sum(Weekly Sales), and Weeks' Cover=sum(Stock)/sum(Weekly Sales).

There is one straight table that has a great deal of detail in it and applies to all 5 Plants.

But I want to create a specific Summary Table that applies to ONLY Plants A, B and C, not showing D and E at all.

PlantStockWeekly SalesWeeks' Cover
A1535.0
B2082.5
C30103.0

I want to make the Plant Dimension show all 3 Plants, regardless of whether a Plant might be excluded due to other dimensions' selection, i.e. if the Plant isn't relevant to the selection then the sum(Stock) should show 0, and so should the Weekly Sales.

My problem is, I can't seem to get all 3 Plants to remain showing.

A Calculated Dimension that half works is, If(Plant='A' or Plant='B' or Plant = 'C', Plant, null()) - meaning that Plants D and E will not show (as long as null dimensions are suppressed). However, as soon as a selection occurs that excludes one of the wanted Plants (let's say this is because a particular Product is selected and it isn't stocked in Plant C) , then the row with C in it disappears. I want Plant C to show, sum(Stock) to = 0, and sum(Weekly Sales) also 0 (Weeks' Cover would show -). Really, I just want the row headed by C to remain.

I think I need to have an aggr() statement in the Calculated Dimension, and somehow refer to the Complete Set {1}, modified to exclude Plants D and E.

But I can't seem to achieve that and I don't know whether it's because my syntax is wrong - highly likely! - or because it's just not possible.

Can anyone help me here?

Thanks and regards,

Pete

1 Solution

Accepted Solutions
Digvijay_Singh

7 Replies
jonathandienst
Partner
Partner

Using set expression in the calc dimension to prevent selection of plant affecting summary:

Aggr(Only({<Plant = {'A', 'B', 'C'}>} Plant), Plant)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner
Partner

Or exclude specific plants

Aggr(Only({<Plant -= {'D', 'E'}>} Plant), Plant)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
peter_burgess
Contributor III
Contributor III
Author

Thanks Jonathan.

I want to aggregate over the complete set, so modified your formula to read:

Aggr(Only({1 <Plant = {'A', 'B', 'C'}>} Plant), Plant)

But - this still doesn't aggregate over the whole set, somehow I need to poll all the Plants that are available. I need something like:

Aggr(Only({1 <Plant = {'A', 'B', 'C'}>} Plant), {1} Plant)   or

Aggr({1} Only({1 <Plant = {'A', 'B', 'C'}>} Plant), Plant)

but of course neither of these has the correct syntax.

Pete

Digvijay_Singh

You may try something like this -

What does it mean sum({1} 0)?

swuehl
MVP
MVP

Remember that you disable 'suppress zero values' on presentation tab (I guess your expressions return zero for dimension values that are excluded by user selection, which leads to removal of that lines by that presentation option).

edit: also, adding an expression with a constant 1 and hiding the expression might help

peter_burgess
Contributor III
Contributor III
Author

Thanks Digvijay, this gave me the result I wanted.

By adding +Sum({1} 0) to the end of both my expressions for Stock and Weekly Sales, and going back to my original calculated dimension formula of  If(Plant='A' or Plant='B' or Plant = 'C', Plant, null()), then the wanted Plants always display, even if they have 0.00 as a value.

There is always a way!!

Pete

Digvijay_Singh

Credit goes to our legends swuehl / stalwar1, as I remember they suggest these tricks quite often here.