How to show only when at least one month is negative

[#1]

Hi, I made this pivot table and I must select all items that have storage negative even if only for a month.

So the pivot must show only the item '015A02S- 902' with the storage of the month (positive and negative)

The expression is now SUM(QTMO), I try to made an expression that match the sum of the selected month with the sum of the absolute value of the selected month but it does not work.

SUM(IF(AGGR(SUM(FABS(AGGR(SUM(QTMO), CDAR, AAMM))), CDAR)=

             AGGR(SUM(           AGGR(SUM(QTMO), CDAR, AAMM)),  CDAR), 0, QTMO)

Can anybody help me?

[captain89]

I've made a KEY with CDAR and AAMM and then i put this expression:

sum({<KEY={"=sum([QTMO])<0"}>}[QTMO])

but in this way qlik dislays only the record with -5438 without the other months.

[martynlloyd]

Hi Matteo

I'm sure someone will come up with the expression - but for me it is much more sensible to tag the data in the script, and then use a filter or set analysis.

Something like:

NegativeMonths:  // or join to existing table... //

Load DISTINCT

Code

,'Y' as Negative Month

Resident YourTable

Where MonthValue<0;

Marty.

[captain89]

Item	Store	Quantity
A01	ST.1	-100
A01	ST.2	+110
A01	ST.3	-50
A02	ST.2	-10

I can't, because the negative value depends by selections.

Items are stored in many warehouses, if I select only ST.1 and ST.2 A01 is positive.

But if I tag the item in the script I must consider all stores.

Is it true or not?

[martynlloyd]

Hi Matteo,  how are you getting on with your problem?

It should be easy to identify a negative in any location, and flag the ITEM.

The distinct keyword will limit the list to unique Items

Could you post a bit more detail about your data structure, some data and the load script?

Marty.