topic Re: Set Analysis: ignore field selection in sum, then define select using variable in App Development

Set Analysis: ignore field selection in sum, then define select using variable

johannes_myers — Fri, 08 Mar 2019 22:06:39 GMT

I'm trying to display only the most recent 13 months of data in a bar chart, based on the maximum selected date (max date if nothing selected). So far I've tried this is what I've come up with:

=Sum({$<[Function]={"Billable"},[Year]=, Period={"<=$(MAX_PERIOD_ID)"}>} Actual)

The problem is that only one month shows up in the chart when the user selects one month. I know the '$' means the set analysis will respect user selections, and that's fine for all of the fields EXCEPT for Year and Period. Is there a way to tell Qlik to only ignore the user selections in specific fields?

Thanks in advance,

Johannes

Re: Set Analysis: ignore field selection in sum, then define select using variable

jayanttibhe — Fri, 08 Mar 2019 22:35:21 GMT

You already ignored 'Year' in your expression. Do the same for period Or whatever fields you wanted to ignore.

This is what you are looking for ?

=Sum({$<[Function]={"Billable"},[Year]=, Period={"<=$(MAX_PERIOD_ID)"},Period=>} Actual)

Re: Set Analysis: ignore field selection in sum, then define select using variable

VishalWaghole — Sat, 09 Mar 2019 05:06:15 GMT

Try this,

=Sum({$<[Function]={"Billable"},[Year]=, Period={'>=$(=MonthStart(AddMonths(Max(Period),-13))) <=$(=MonthStart(Max(Period)))'}>} Actual)

Thanks,
Vishal

Re: Set Analysis: ignore field selection in sum, then define select using variable

johannes_myers — Mon, 11 Mar 2019 13:04:20 GMT

Unfortunately that did not work. If the user filters to only one value in the "Period" field, the chart still only shows one bar when using the syntax you suggested.