Set Analysis Help

[mahitham]

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

[JordyWegman]

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

[sunny_talwar]

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())

[mahitham]

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