Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
janus2021
Contributor III
Contributor III

GetFieldSelection in

Hi.
I use GetCurrentSelecton to get dynamically rolling months backwards according to the following code:
If (
[Period] <= GetFieldSelections (Period)
and
[Period]> GetFieldSelections (Period) -100
,Period
)

It works great as long as you only choose one period in the filter.

Is it possible to make it so that if the user selects 202010, 202011 and 202012 in the filter, the function only returns the highest value (use 202012 as Period) or the lowest(use 202010 as Period)?
I have tried to use Min(Period) and Max(Period) in the function but only gets errors.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

=If(
Period <= Max(total Period)
and
Period > (Max(total Period)-100)
,Period
)

View solution in original post

7 Replies
GaryGiles
Specialist
Specialist

If you have a field with the same value for all Periods, you could use an aggr() and FirstSortedValue, like this:

If (
[Period] <= aggr(FirstSortedValue(Period,-Period), fieldname)
and
[Period]> aggr(FirstSortedValue(Period,-Period), fieldname) -100
,Period
)

The aggr() function needs a field to aggregate over.  You can't use a literal.

If you don't have a field, you could add a field like this to your period table in the load script.

'1' as aggrfield

Another option would be to use a system field like $Info, although I'm not sure of the performance impact.

If (
[Period] <= aggr(FirstSortedValue(Period,-Period), $Info)
and
[Period]> aggr(FirstSortedValue(Period,-Period), $Info) -100
,Period
)

 

Vegar
MVP
MVP

Instead of comparing to Min(Period) try to compare with $(=Min(Period)). It might help.

janus2021
Contributor III
Contributor III
Author

Tried the proposal but it only gave a flat line even when I only chose one Period. I also created the aggrfield.

janus2021
Contributor III
Contributor III
Author

Do you mean like this: 
If(
[Period] <= GetFieldSelections($(=Min(Period)))
and
[Period]> GetFieldSelections($(=Min(Period)))-100
,Period
)

Unfortunately, it did not work.

 

tresesco
MVP
MVP

For dynamic rolling period calculation this approach might not be the best. I am not going into what is wrong with the expression you are trying, because even we resolve that - your actual problem might still persist. I would recommend you to create an app with sample data set and explain your expected output w.r.t. that; you might get to the solution faster. 

janus2021
Contributor III
Contributor III
Author

Hi.
I have created an app and attached it, it shows the problem.
Shows how rolling 12 months works when you only make one choice
in the filter but not if you make two choices.

tresesco
MVP
MVP

Try like:

=If(
Period <= Max(total Period)
and
Period > (Max(total Period)-100)
,Period
)