Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bbastro1106
New Contributor III

Not able to ignore current filter selection in set analysis

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?

 

3 Replies
Channa
Valued Contributor III

Re: Not able to ignore current filter selection in set analysis

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])

Channa
bbastro1106
New Contributor III

Re: Not able to ignore current filter selection in set analysis

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
Valued Contributor III

Re: Not able to ignore current filter selection in set analysis

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])

Channa