Pivot Table - Suppressing items

[joshabbott]

Hello,

This seems simple enough but I can't seem to find an answer.  I have 3 dimensions:

1 - Employee

2 - Month/Year

3 - Week

And 1 expression

sum(hours)

I want to supress at the Month/Year level if the total hours is less than 50 at the month/year level.  Can anyone give me an idea of how I would do that?

Thank you!

[its_anandrjs]

Hi,

What do you mean by supress if you mean you want to supress null between for Month/Year or if load data conditionally like

=Sum( if(Sum(hours)<50, hours)

Write some thing like this or clear your requirements.

Regrads,

Anand

[joshabbott]

Thank you for the reply.  I have attached an example.  Here is what my pivot table looks like below:

Employee - Month Year - Week - Hours

John - 1/2011 - 1 - 20

John - 2/2011 - 1 - 40

John - 2/2011 - 2 - 40

I want record number 1 not to even show in the pivot table.  I don't believe I can do anything in the load because the other data is needed in other parts of the application.

[its_anandrjs]

Hi,

You dont want to display record first right means this one

John - 1/2011 - 1 - 20

But what is the condition for this.

Regards

Anand

[joshabbott]

Correct.  I don't want to display the record if the total at the month/year level is < 50.

Employee - Month Year - Week - Hours   -> Total @ Month/Year Level

John - 1/2011 - 1 - 20  -> 20 (Don't show because the total @ month/year level is only 20)

John - 2/2011 - 1 - 40  -> 80

John - 2/2011 - 2 - 40  -> 80

Thank you!

[IAMDV · Accepted solution]

Hi Josh,

You need to use the Aggr() function in your expression.

IF(Sum(Aggr (sum(Hours), MonthYear)) < 50, null(), sum(Hours))

I am also attaching the sample report. Do let me know if you need something else.

Cheers - DV

[its_anandrjs]

Hi,

But you say the record if the total at the month/year level is < 50 you dont want to display

and another point you say  total @ month/year level is only 20, it  will getting confuse.

Regards

Anand

[joshabbott]

Sorry for the confusion.  The aggr function worked perfectly.  Thank you all for your help!

[its_anandrjs]

Hi,

See the attached sample might it work for you.

Regards

Anand