topic Re: Ignore selection in an Aggr function in QlikView

Ignore selection in an Aggr function

Thu, 18 Feb 2016 12:31:26 GMT

Hi,

can someone assist me , i have the following expression i want it to ignore the year and month selection:

Where should i put the year and month dimension , ive been trying for long .

SUM((aggr(sum([Quantity On Hand])*sum([Unit Cost]),WareHouse,InventoryStockCode)))

Re: Ignore selection in an Aggr function

kaushiknsolanki — Thu, 18 Feb 2016 17:43:56 GMT

Hi,

Try this

SUM((aggr(sum({<Year = ,Month = >}[Quantity On Hand])*sum({<Year = ,Month = >}[Unit Cost]),WareHouse,InventoryStockCode)))

Regards,

Kaushik Solanki

Re: Ignore selection in an Aggr function

sunny_talwar — Thu, 18 Feb 2016 18:09:02 GMT

Just to be safe, I would add set analysis to the first Sum as well

Sum({<Year, Month>}Aggr((Sum({<Year, Month>}[Quantity On Hand])*Sum({<Year, Month>}[Unit Cost])), WareHouse, InventoryStockCode))

Re: Ignore selection in an Aggr function

Thu, 18 Feb 2016 18:30:36 GMT

Hi Sunny

Thanks your suggestion worked fine ....You are a star !!!!

Regards

Innocent

Re: Ignore selection in an Aggr function

sunny_talwar — Thu, 18 Feb 2016 20:57:54 GMT

I am glad I was able to help, please close this thread if you got what you were looking for by marking correct and helpful answers. Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny

Re: Ignore selection in an Aggr function

hirenjadiya — Fri, 01 Jun 2018 22:44:37 GMT

Hi Sunny,

Thanks for the above solution. It helped me to ignore selection while doing AGGR() Function.

Regards,

Hiren Jadiya

Re: Ignore selection in an Aggr function

lalita_sharma — Tue, 07 Sep 2021 10:57:33 GMT

Hi All

I have a similar expression where I want to ignore anything selected in Date filter. It doesn't seem to work.

Could you please help?

=IF(GetSelectedCount(Workstream)=0,
Aggr(only({$<[Workstream Id]={'$(vCalc_StatusPack_DefaultWorkstreamID)'},[KPI name]={3}, Date =>}[Workstream]),[Workstream],[Workstream Id],[Risk Id],[KPI name],Date),
Aggr(only({$<[KPI name]={3},Date =>}[Workstream]),[Workstream],[Workstream Id],[Risk Id],[KPI name],Date))

My data looks like below

Risk Id	Workstream	Workstream Id	KPI name	Date
-	C	1	1	03/09/2021
-	C	1	2	27/08/2021
R004	C	1	3	10/07/2021
R007	C	1	3	10/07/2021
-	A	5	1	03/09/2021
-	A	5	2	27/08/2021
R027	A	5	3	10/07/2021
R028	A	5	3	10/07/2021

I am trying to create a straight table like below

Risk Id	Workstream	Workstream Id	KPI name	Date
R004	C	1	3	10/07/2021
R007	C	1	3	10/07/2021
R027	A	5	3	10/07/2021
R028	A	5	3	10/07/2021

the expression is for Workstream column. I dont want the data in this table to be affected by date filter selection.