topic Re: Ignoring all filters in set analysis in App Development

Ignoring all filters in set analysis

orangebloss — Fri, 07 Mar 2025 10:33:50 GMT

I need a static KPI that doesn't change when any filters are used throughout my app,

reading around it looks like the key is using {1} but I'm evidently not getting it in the right place as it's not working!

Current format is as below which returns a null when a filter is selected:

((Sum({1}{$<TdateYear={"(=$(vCurrentYear))"},NPD_Other={'Overhead'}>} Nmhrs))+ (Sum({1}{$<TdateYear={"(=$(vCurrentYear))"},NPD_Other={'Overhead'}>} OThrs)))/

((Sum({1}{$<TdateYear={"(=$(vCurrentYear))"}>} Nmhrs))+ (Sum({1}{$<TdateYear={"(=$(vCurrentYear))"}>} OThrs)))

Re: Ignoring all filters in set analysis

Ben_P — Fri, 07 Mar 2025 10:44:06 GMT

Hi, try using the 1 to replace the $... so instead of this :

Sum({1}{$<TdateYear=...

You have this:

Sum({1<TdateYear=...

There is some more information in the 'modifiers' section of this page: Set analysis and set expressions | QlikView Help

Re: Ignoring all filters in set analysis

marcus_sommer — Fri, 07 Mar 2025 10:44:59 GMT

You may try:

Sum({1 $<TdateYear={"(=$(vCurrentYear))"},NPD_Other={'Overhead'}>} rangesum(Nmhrs, OThrs)) /
Sum({1 $<TdateYear={"(=$(vCurrentYear))"}>} rangesum(Nmhrs, OThrs))

Re: Ignoring all filters in set analysis

orangebloss — Fri, 07 Mar 2025 11:13:58 GMT

Thanks both - I've tried to simplify by adding the calculations to variables - would this be a better way to do it and if so where does the 1 go?

sum(

v_Overhead_LastYear/ v_AllTime_LastYear)

Re: Ignoring all filters in set analysis

orangebloss — Fri, 07 Mar 2025 11:16:47 GMT

Thanks Marcus - does there need to be a closing } for the {1 ?

Re: Ignoring all filters in set analysis

Ben_P — Fri, 07 Mar 2025 11:19:42 GMT

The 1 is a set identifier, so goes at the beginning of the set analysis, after a {

The closing } comes at the end of the set analysis expression.

Re: Ignoring all filters in set analysis

orangebloss — Fri, 07 Mar 2025 11:25:18 GMT

I can't get the range sum to work? it seem s to want an extra }

Re: Ignoring all filters in set analysis

Ben_P — Fri, 07 Mar 2025 11:30:35 GMT

Also, your example where you have used variables... you can apply an outer set analysis expression at the start of the formula, so it then applies to the whole thing. Note this will depend on how your variables are defined, i.e. you need to make sure they aren't pre-evaluated; they need to evaluate as part of your final combined formula.

Inner and outer set expressions | Qlik Sense on Windows Help

Re: Ignoring all filters in set analysis

marcus_sommer — Fri, 07 Mar 2025 11:33:15 GMT

I think there were not all causes fetched - therefore try it with:

Sum({1 <TdateYear={$(vCurrentYear)},NPD_Other={'Overhead'}>} rangesum(Nmhrs, OThrs)) /
Sum({1 <TdateYear={$(vCurrentYear)}>} rangesum(Nmhrs, OThrs))

Re: Ignoring all filters in set analysis

marcus_sommer — Fri, 07 Mar 2025 11:44:18 GMT

Variables are a very powerful feature - but they are adding complexity because there are extra dependencies to how a variable is created + where they is called again + which kind of content is included.

What ever - variables shouldn't be the starting point else the end. Means if there is a working expression/logic which is either very large and/or quite redundant it might be worth to outsource into a variable - because of the fact that the working were already proofed the sometimes challenging syntax-stuff isn't so hard - but the opposite way could be quite painful.

Re: Ignoring all filters in set analysis

orangebloss — Mon, 10 Mar 2025 13:30:41 GMT

Thanks Marcus - why would the rangesum give me a different result to my original total ?

Re: Ignoring all filters in set analysis

marcus_sommer — Mon, 10 Mar 2025 13:56:46 GMT

By combining values with the normal math operators each non-numeric result for one of them will produce NULL for the entire calculation-level. The range-functions instead handle each non-numeric parameter as ZERO. Within the most scenarios is this type of error/exception-handling more suitable and expedient as getting NULL.