Preventing totals for text expresions on Pivot tables

[#1]

have a pivot table which has products with their planned source and destination with the associated costs....

I have a 2nd table which has other ' sourcing options' with their associated costs. I calculate the sources that would be the least expensive ( Source 1, Source 2, Source 3) using an expression but the end result is a name of a location (i.e florida, colorado, california, etc) but in my pivot table when everything is aggregated up to the product level, it shows the "Total" for the  Source 1 as Florida, which really makes no sense.

I want it to show the  Source 1, 2, 3 only when I've expanded from Product to Destination.

Any idea on how to go about this?

[#2]

Kim, I still see the  "1B" under Option 1 .....

[Anonymous]

Sorry, I misunderstood what you wanted help with.

You can probably use the "Dimensionality()"-function, to check what level of the pivot you are on, and restrict to a certain level. See the example. I added Dimensionality() as its own expression as well, for you to see how it works. You can remove it when you want.

See the attached example

[#4]

This seems to be working pretty darn well. I have a question though, what does the last row show exactly? See attached picture. How can I remove it?

[Anonymous]

Hi,

This has to do with your data modeling, and especially that you have a syntetic key between the product, source and destination field (see http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys for some more info on syntetic keys).

I would recommend remodeling to remove the syntetic key first.

You could remove that line in the object itself by checking "Suppress when value is NULL" for the calendar_week dimension under the Dimensions tab, even though i would recommend remodeling instead.

[#6]

Thank you Kim. I also have an IF statement that should not be shown unless dimensionality > 4 .

If([Destination Location]=Option 1,'Y','N')

I need to combine the above with the following: If(Dimensionality() >4

Anyone know how I can do this?

[Anonymous]

If([Destination Location]="Option 1" and Dimensionality() >4,'Y','N') should do the trick.

[#8]

It is not working. Let me see if I can give you a sample app

[#9]

Kim,

I've attached a sample example where the Source = option 1, Source = option 2, Source = option 3 isn't being calculated correctly and it doesn't obey If dimensionality () > 4

Please help.

[#10]

I realize that it calculates correctly if you expand to the 4th dimension. I think another function needs to be used here.

Can anyone help on this?

[sorrakis01]

Hi,

An idea,

If you like to remove (HIDE) this row, you need to modify your expressions and mark the option in dimension Supr. when value is null.

Change all your expressions:

if(isnull(calendar_week),null(),Sum ([Cost 1 A]))

if(isnull(calendar_week),null(),Sum ([Cost 2 B]))

When all values of a row are null the pivot don't show it.

Regards