Pivot Table without column with zero value.

[#1]

Hi

i have create a pivot table to show the  hour of production and the hour of block group by causal of block.

Now in the block period, the quantity is zero, but in the pivot for the colums of block /quantity is printing.

Can you suggest some mode to hidden it?

I attached one printscreen of my presentation.

Thaks

Federico

[JonnyPoole]

Not really enough controls to do this simply. It is doable however .

To suppress the measure conditionally i think you need to model and handle it like a dimension. Then you can evaluate the dimensions conditionally , set to NULL if the condition is false (=0) and disable 'show null values'

First you need to pivot the data in the data model to make your measures a dimension:

Country, Month, Sales, Costs

USA,2009-Jan,100,50

UK,2009-Feb,90,40

into

Country, Month, MeasureName,MeasureValue

USA,2009-Jan,Sales,100

USA,2009-Jan,Costs,50

UK,2009-Feb,Sales,90

UK,2009-Feb,Costs,40

Then in the UI, remove your two existing measures and add 'measurename' as a new column dimension

Make your measure  sum(MeasureValue)  ... or a conditional expression based on measurename value.

To start suppressing the measure with zeroes you have to change the measurename dimension into a calculated dimension that checks if the column total is zero or not. Below i am doing it for column total > 1500.

You can see the side by side differences (typo in title.. should say < 1500 not < 2000)

[#3]

Thank you Jonathan !!

I have some dubs, in your case you have modified the table with a new field, is it right?

In my case i have two fields "Durata" and "Colpi".

The Durata means the time of production or block, and the second "Colpi" is the quantity product.

Now I have the column "Causale descrizione" that is the aggregation.

The Field "Colpi" contains zero is the Causale Descrizione is not work.

Could i ask you how to suggest to create the aggregation?

Thanks

Fede

[#4]

Sorry I didn't say that in the case of Work I must show in this columns both values Durata and Colpi.

In the other case the column colpi is always zero, and i would like suppress it.

[JonnyPoole]

Can you upload your QVF ?  Otherwise its too difficult  to advise how to do this exactly on a data model i haven't seen and with measure expressions that i have not seen. 

[#6]

Yes Of course!!!

Sorry But i don't Know How to upload QVF in the discussion.

I'm Novice...

[JonnyPoole]

You have to

1. Click the thread (don't just reply) to open up the full thread

2. Then hit reply

3. Then use 'advanced editor' to upload files

[#8]

Hi Jonathan

attached you can find the QVF (It is My the first QVF).

Now in the first sheet, you find the table "Time & Qty Production Group By Item/causal".

Thank you.

Fede

[#9]

Jonathan Is it visible my QVF?

[JonnyPoole]

i see it.  i'm on community for only a few mins at a time but i'll see what i can do.