topic Re: Calculation condition in a pivot table in QlikView

Calculation condition in a pivot table

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

I really need to restrict a pivot table to only show data from a specific data set.

I have tired using this expression in the Calculation Condition box but it isn't restricting.

=if(Type='Non-Blue Box',1,0)

When I select the option myself manually through a list box, it does restrict.

Any ideas?

Re: Calculation condition in a pivot table

Anil_Babu_Samineni — Thu, 04 May 2017 08:31:57 GMT

May be try this?

Pick(Match(Type, 'Non-Blue Box'),1,0)

Re: Calculation condition in a pivot table

jonathandienst — Thu, 04 May 2017 08:35:17 GMT

I assume that Type is a field that has more than one possible value. [Type] will evaluate to null unless you restrict it to one possible value. To see if 'Non-Blue Box' is a possible value for Type, you can do something like

=Index(Concat(Type, ','), 'Non-Blue Box') > 0

Re: Calculation condition in a pivot table

khaycock — Thu, 04 May 2017 08:35:27 GMT

No change

Re: Calculation condition in a pivot table

Anil_Babu_Samineni — Thu, 04 May 2017 08:37:57 GMT

Can you share sample data and tell us where you are doing and what you indeed

Re: Calculation condition in a pivot table

khaycock — Thu, 04 May 2017 08:38:14 GMT

It actually is one value as I created the field in the script to only restrict this table. I tried this statement anyway and still was no change

Re: Calculation condition in a pivot table

jonathandienst — Thu, 04 May 2017 08:42:07 GMT

Then I don't fully understand your environment. If Type a field or value? Does it have a single possible value? The more information you provide, the more accurate will be the answers.

Re: Calculation condition in a pivot table

khaycock — Thu, 04 May 2017 08:52:43 GMT

Here is a sample. Only week 8 data should be shown as that is the only Non-Blue Box data I have.

Thanks

Re: Calculation condition in a pivot table

khaycock — Thu, 04 May 2017 09:31:19 GMT

I have attached a sample file above which may be of help

Re: Calculation condition in a pivot table

vinieme12 — Thu, 04 May 2017 10:16:43 GMT

You need to restrict the data in Set Analysis or using Calculated Dimension

Example using Set Analysis of the Expressions

SUM({<type={'Non-Blue Box'}>} SALES)

Example using calculated dimensions

AGGR(if(type='Non-Blue Box',Fieldname),Fieldname)

Re: Calculation condition in a pivot table

vinieme12 — Thu, 04 May 2017 10:19:27 GMT

See attached updated app, just add Set Analysis

Re: Calculation condition in a pivot table

tresB — Thu, 04 May 2017 10:26:59 GMT

Check attached qvw.

I have included set analysis section {<Type={'Non-Blue Box'}>} in all aggregation functions you used both in variables and direct expressions.