Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jelindbe
Partner
Partner

Retrieving years minus 1

I try to make an expression to retrieve values based on selected years, but where the year value are subtracted with one year. For example when selecting the year 2019, I want values for 2018. When I select 2017, 2018, 2019 I want values for 2016,2017,2018.

I have made this expression:

Sum({<Year={$(=GetFieldSelections(Year)-1)}>}Sales)

It works fine when selecting one year. For example, if I select 2019 it gives me 2018.

But when I select several years (2019, 2018, 2017) it ends in nothing.

 

Labels (1)
2 Solutions

Accepted Solutions
sunny_talwar

Another option is to use this

Sum({<Year = {">=$(=Min(Year)-1)<=$(=Max(Year)-1)"}>} Sales)

View solution in original post

vunguyenq89
Creator III
Creator III

@jelindbe  I figured out the correct expression now. It's much simpler than I initially thought 😀.

Concat(Aggr(...))  can be used in the set modifier to form the search string:

 

 

=Sum({<Year={$(=Concat(Aggr(Max(Year)-1,Year),','))}>}Sales)

 

 

 This one works with both range and discrete selections:

result.png

BR,

Vu Nguyen

View solution in original post

6 Replies
vunguyenq89
Creator III
Creator III

Hi, 

The following expression works, but only when you select a connected range of year (for example 2017,2018,2019)

=Sum({<Year={">=$(=Left(GetFieldSelections(Year),4)-1) <=$(=Right(GetFieldSelections(Year),4)-1)"}>}Sales)

The expression inside Set Analysis generates a search string like ">=2016 <=2018", which can be applied  as a set modifier.

This doesn't work if you also need to select discrete years (for example 2016, 2017, 2019) because the generated search string would be ">=2015 <=2018", which means 4 years 2015,2016,2017,2018. 

Hope this helps,

BR,

Vu Nguyen

sunny_talwar

Another option is to use this

Sum({<Year = {">=$(=Min(Year)-1)<=$(=Max(Year)-1)"}>} Sales)

View solution in original post

vunguyenq89
Creator III
Creator III

Yes of course, I over-complicated the solution. This is a better one 🙂

jelindbe
Partner
Partner
Author

Thank you!

vunguyenq89
Creator III
Creator III

@jelindbe  I figured out the correct expression now. It's much simpler than I initially thought 😀.

Concat(Aggr(...))  can be used in the set modifier to form the search string:

 

 

=Sum({<Year={$(=Concat(Aggr(Max(Year)-1,Year),','))}>}Sales)

 

 

 This one works with both range and discrete selections:

result.png

BR,

Vu Nguyen

View solution in original post

jelindbe
Partner
Partner
Author

You are right! That one worked. 

Perhaps you could come up with a nice solution for my label.

So far I have this solution: 'my variable description ' &(Min(Year)-1) &'-' &(Max(Year)-1). This was OK for range selections, but is not good for discrete selections.

All the best and good weekend!