topic Re: Pivot Table without column with zero value. in App Development

Pivot Table without column with zero value.

Wed, 18 Nov 2015 15:24:48 GMT

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

Re: Pivot Table without column with zero value.

JonnyPoole — Wed, 18 Nov 2015 16:07:17 GMT

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)

Re: Pivot Table without column with zero value.

Wed, 18 Nov 2015 17:42:27 GMT

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

Re: Pivot Table without column with zero value.

Wed, 18 Nov 2015 17:47:38 GMT

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.

Re: Pivot Table without column with zero value.

JonnyPoole — Wed, 18 Nov 2015 17:58:47 GMT

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.

Re: Pivot Table without column with zero value.

Wed, 18 Nov 2015 18:11:10 GMT

Yes Of course!!!

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

I'm Novice...

Re: Pivot Table without column with zero value.

JonnyPoole — Wed, 18 Nov 2015 18:28:50 GMT

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

Re: Pivot Table without column with zero value.

Thu, 19 Nov 2015 07:12:07 GMT

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

Re: Pivot Table without column with zero value.

Thu, 19 Nov 2015 15:06:20 GMT

Jonathan Is it visible my QVF?

Re: Pivot Table without column with zero value.

JonnyPoole — Thu, 19 Nov 2015 17:35:05 GMT

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

Re: Pivot Table without column with zero value.

Tue, 24 Nov 2015 09:16:46 GMT

Hi, Have you find some solution for my question?

Thank a lot,