topic Pivot Table - Suppressing items in QlikView

Pivot Table - Suppressing items

joshabbott — Tue, 06 Sep 2011 13:20:54 GMT

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!

Pivot Table - Suppressing items

its_anandrjs — Tue, 06 Sep 2011 13:31:29 GMT

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

Re: Pivot Table - Suppressing items

joshabbott — Tue, 06 Sep 2011 13:38:53 GMT

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.

Pivot Table - Suppressing items

its_anandrjs — Tue, 06 Sep 2011 14:03:23 GMT

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

Pivot Table - Suppressing items

joshabbott — Tue, 06 Sep 2011 14:06:38 GMT

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!

Re: Pivot Table - Suppressing items

IAMDV — Tue, 06 Sep 2011 14:25:08 GMT

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

Re: Pivot Table - Suppressing items

its_anandrjs — Tue, 06 Sep 2011 14:30:18 GMT

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

Re: Pivot Table - Suppressing items

joshabbott — Tue, 06 Sep 2011 14:31:54 GMT

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

Re: Pivot Table - Suppressing items

its_anandrjs — Tue, 06 Sep 2011 14:34:58 GMT

Hi,

See the attached sample might it work for you.

Regards

Anand