How to return true/false when checking several dimensions and string values and in a pivot?

[SupplyAndDemand]

INTRODUCTION:

I am a logistics controller and in the last year I have create an app that shows exception report for local planners when there are some parameters that deserve a closer look in their warehouse.

Now I am in the phase of connecting these into a global exception report in order to get real control, but it makes it a bit more difficult when checking values on different levels and dimensions.

THE PROBLEM

1. How can I set up an expression in a pivot table that will return true/false if one manufactured_item is not active/active in any warehouse?
2. How can I set up an expression in a pivot table that will return true/false if one bom_item is not active/active in any warehouse for all related manufactured_items?

EXAMPLE

I tried setting up a simplified example:

 Definitions:

• Manufactured_item is made from the bom_item
• Global strategy:
• Active
• Not active on one level
• Not active globally
• Local strategy:
• Active: Stocked, On demand, Special
• Not Active: null, Discontinued, Pending check, Not applicable

 What I would like to do:

Make several checks to see where the local and global strategy is not connected. For instance:

1. Check where a manufactured item is not active in any warehouse, but are active globally
2. Where a bom_item is active but none of the related manufactured items are active in any warehouse
3. Where the global item is Special, but some of the local strategies are not.

The enclosed QlikView example:

• Item1: is correct
• Item 2: The global strategy is wrong since item2-2 is active. So I would like to set the global strategy as active on Item2-2 and “not active on one level” on Item2-1
• Item 3: All manufactured_items are not active in any warehouse, so the global strategy is wrong and should be changed to not active globally

[sunny_talwar]

global_strategy is a dimension, so are you looking to handle this in the script?

[SupplyAndDemand]

I would optimally like to handle this in an expression in a chart if it is possible, but if using the script is the only way, I need to do it there.

[sunny_talwar]

What I am trying to understand is that you have a value for Global_strategy... you want to keep this value as is in the database, but only override this on the front end using an expression or are you looking to fix this in the data as well?

[SupplyAndDemand]

I am only looking to get a report listing all items that does not fit my criteria. The planner will then use this report to "clean up" the data directly in my ERP system.

[sunny_talwar]

Right, but you are bringing global_strategy from a database or is that something your create in the script?

[SupplyAndDemand]

All the information in the example are directly retrieved from a database (ERP-system). The local and global strategies are string fields that can be changed by a person in the ERP-system, but the rest are fixed relationships and connections.

[sunny_talwar · Accepted solution]

So, this will only work for the chart and might contradict what you bring from the script... but try to use this as your calculated dimension

=Aggr(
	If(
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'Stocked') = 1 or
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'On demand') = 1 or
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'Special') = 1,
	'Active', 'Not Active')
, bom_item)

[SupplyAndDemand]

Thank you so much, it seems to work on my real data. 

It is a bit messy, but I think I can filter out my Special items like this: 

=if(
(Aggr(
	If(
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'Stocked') = 1 or
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'On demand') = 1 or
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'Special') = 1,
	'Active', 'Not Active')
, bom_item))='Active'
and
(Aggr(
	If(
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'Stocked') = 1 or
		SubStringCount(Concat(DISTINCT '|' & local_strategy & '|'), 'On demand') = 1,
	'Not Special', 'Special')
, bom_item))='Special','Special','Not Special')