Not able to ignore current filter selection in set analysis

[bbastro1106]

Hi All,

I had a filter under my sheet to select the "Yearly_Erd In DC Date".  There are number of KPI and report which would like to exclude this filter.

Previously i have below expression which is work to calculate the Short shipment as below:

Count(Distinct {<[PO Number]={"=Sum({$<[PO Total Units] = - {0}>} [PO Total Units]-[Original Units])<0"}>} [PO Number])

This is to do a count on PO number, if the PO total units is not = 0 and also when PO total units - original units is smaller than 0 then perform a count of unique PO number

I also have another expression which is work to calculate the total cost  if the "Yearly_Erd In Dc Date" of each record =  Max([Yearly_Erd In Dc Date])-1 , also also sum up if PO total unit not = 0

=Sum({$<[Yearly_Erd In Dc Date]={"$(=Max([Yearly_Erd In Dc Date])-1)"} ,[PO Total Units] -={0} >} [PO Total Units] *[SELL COST])

With both of the expression are working perfectly, i try to combine both express in order to do a count on Unique PO number, only if below criteria:

1 - PO Units <>0

2- [Yearly_Erd In Dc Date] = Max(Yearly_Erd In Dc Date)-1 

          Given Yearly_Erd In Dc Date is show in record like 2018, 2019, 2020

3- PO unit - Original Unit is smaller than 0

by then, i have below expression

Count(Distinct {$< [Yearly_Erd In Dc Date]={"$(=Max([Yearly_Erd In Dc Date])-1)"} , [PO Number]={"=Sum({$<[PO Total Units] = - {0}>} [PO Total Units]-[Original Units])<0"}>} [PO Number])

If i did not select any filter on  [Yearly_Erd In Dc Date] from the sheet filter, i am able to get the result correctly.  However, if i select something under the  [Yearly_Erd In Dc Date] filter, the result will show me 0.

Can anyone please help what did i do wrong on my expression?

[Channa]

if you want to exclude filter selection

try to have alias for [Yearly_Erd In Dc Date] and use  

=Sum({$<[Yearly_Erd In Dc Date]=, [Yearly_ErdDate]={"$(=Max([Yearly_Erd In Dc Date])-1)"} ,[PO Total Units] -={0} >} [PO Total Units] *[SELL COST])

[bbastro1106]

the expression i have issue is below

Count(Distinct {$< [Yearly_Erd In Dc Date]={"$(=Max([Yearly_Erd In Dc Date])-1)"} , [PO Number]={"=Sum({$<[PO Total Units] = - {0}>} [PO Total Units]-[Original Units])<0"}>} [PO Number])

and i do not want to make any changes under the scripts.

[Channa]

ur counting PONUmber??

Count(Distinct {$< [Yearly_Erd In Dc Date]={"$(=Max([Yearly_Erd In Dc Date])-1)"} , [PO Number]={"=Sum({$<[PO Total Units] = - {0}>} [PO Total Units]-[Original Units])<0"}>} [PO Number])

try to put all conditions inside below you will get count of PO Number where Total Unit <>0 and year =max(year)-1

Count(Distinct {< [PO Number]={"=Sum([PO Total Units] )-=0  and [Yearly_Erd In Dc Date]= max(Year)-1"}>} [PO Number])