topic Re: How to avoid filter in measure in Reporting Service & Alerting

How to avoid filter in measure

Manlyhero — Tue, 01 Mar 2022 21:11:16 GMT

Hellow I have a measure which derived from each other:

rangesum(Above((sum({<[type]={'Circle'}>}[amount])) , 1, rowno()))*Avg(1)

This statement gives me the total amounts which type is CIRCLE for every month.

Some of my filters are Year, Month and Date filter and I want to above statement excluded these filters.

At first results like below

but when I select 2002-Jan than Opening will be 0

Any suggestion how to achive that?

Re: How to avoid filter in measure

Lisa_P — Wed, 02 Mar 2022 06:22:55 GMT

Within your set { }, list the fields you want to ignore filter for

rangesum(Above((sum({<[type]={'Circle'}, Year, Month, Date >}[amount])) , 1, rowno()))*Avg(1)

Re: How to avoid filter in measure

Manlyhero — Wed, 02 Mar 2022 19:23:54 GMT

Thank you for your reply but it didn't work. Still when I select the Year or Month or Date opening changes

Re: How to avoid filter in measure

Manlyhero — Thu, 03 Mar 2022 22:32:35 GMT

Hi @Lisa_P I solve my problem by changing the set expression completely I used as of table and followed this blog post and also the view from youtube about the master table.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

https://www.youtube.com/watch?v=ek_ITfvCnHw

But eventually, to exclude the fields I added them new set expression like you suggest because I will accept you answer. Have a nice day