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
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Set Analysis Help

Hi,

Can any one please help me to pass the below variable in set analysis.

I have a Planning field with below records

Planning

2019 Plan

2018 Plan

2017 Plan

Need to qualify latest year Plan dynamically 

So, I have created a vMaxYear variable in variable editor like Year(Date(Today(),'DD/MM/YYYY'))

Then used in below expression but its working.Please can any one correct the below expression 

 = Count({<Planning={$(vMaxYear)&' '& 'Plan'}>}ID)

 

Thanks in advance

3 Replies
Partner
Partner

Hi Mahitham,

You can't use the concatenation in your formula.

What you can do is create another field, with only the year. Use this formula:

Left([Planning],4) as YearPlanning

or

Subfield([Planning],' ',1) as YearPlanning

 

Then use this formula with set analysis:

Count({$< YearPlanning = {"$(vMaxYear)"}>}ID)

 

Btw, your formula for the year can also be: 

Year(Today())

Jordy

Climber

Work smarter, not harder

Try this

=Count({<Planning = {"$(=$(vMaxYear) &' Plan')"}>} ID)

or this

=Count({<Planning = {"$(=vMaxYear & ' Plan')"}>} ID)

Also, you can change your variable to just this

Year(Today())
Creator II
Creator II

Hi  @sunny_talwar 

Thanks for your help.

Is there any way to filter in the backend.

For example Planning contains 4 records like below.

 

Planning

2019 Plan

2018 Plan

2017 Plan

TBC Plan 

 

From these need to filter only Planning ='2019 Plan','2018 Plan','2017 Plan' data by passing variables on Year field dynamically.

Script is like below

Load

ID,

Planning

From [lib://test/Plan.qvd];

 

Thanks in advance