topic Re: restrict filter in setanalysis in QlikView

restrict filter in setanalysis

krish2459 — Sat, 16 Nov 2024 18:21:06 GMT

Hi,

We need to exclude "Business unit" in the below expression.

I have tried but no luck.

Please help me to reslove this.

=Min(Aggr(
Rangesum(Above(Sum( {<Year=, Quarter=, Month=, Week=, [Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount])
/Sum(total {<Year=, Quarter=, Month=, Week=, [Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount]),1,RowNo())),
(Material,(=Sum({1} {<Year=, Quarter=, Month=, Week=, [Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount]),Desc))
))

Thanks...

Re: restrict filter in setanalysis

krish2459 — Thu, 16 Jul 2020 20:47:33 GMT

Any suggestions please.

Thanks..

Re: restrict filter in setanalysis

Kushal_Chawda — Thu, 16 Jul 2020 23:10:31 GMT

You need yo exclude it from each aggregation functions used here. If you want to exclude the selections for [Pstng Date] as well include there as well like Max( {<[Business Unit]>}[Pstng Date])

=Min({<[Business Unit]>}Aggr({<[Business Unit]>}
Rangesum(Above(Sum( {<Year, Quarter, Month, Week, [Business Unit],[Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount])
/Sum(total {<Year, Quarter, Month, Week, [Business Unit],[Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount]),1,RowNo())),
(Material,(=Sum({1} {<Year, Quarter, Month, Week,[Business Unit], [Pstng Date]={">=$(=Date(Max( [Pstng Date])-13))<=$(=Date(Max( [Pstng Date])))"}>} [Posting Amount]),Desc))
))

Re: restrict filter in setanalysis

krish2459 — Fri, 17 Jul 2020 02:10:24 GMT

Thanks for the reply.

I have used the below expressrion but i'm abe to select plant,material,storage location and material description filters.

=Min({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>}Aggr({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>}
Rangesum(Above(Sum( {<Year, Quarter, Month, Week, [Business Unit],[Material Description],Plant,Material,[Storage Location],[Pstng Date]={">=$(=Date(Max({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>} [Pstng Date])-13))<=$(=Date(Max({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>} [Pstng Date])))"}>} [Posting Amount])
/Sum(total {<Year, Quarter, Month, Week, [Business Unit],[Material Description],Plant,Material,[Storage Location],[Pstng Date]={">=$(=Date(Max( {<[Business Unit],[Material Description],Plant,Material,[Storage Location]>}[Pstng Date])-13))<=$(=Date(Max( {<[Business Unit],[Material Description],Plant,Material,[Storage Location]>}[Pstng Date])))"}>} [Posting Amount]),1,RowNo())),
(Material,(=Sum({1} {<Year, Quarter, Month, Week,[Business Unit],[Material Description],Plant,Material,[Storage Location], [Pstng Date]={">=$(=Date(Max({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>} [Pstng Date])-13))<=$(=Date(Max({<[Business Unit],[Material Description],Plant,Material,[Storage Location]>} [Pstng Date])))"}>} [Posting Amount]),Desc))
))

Thanks..