Can we put condition in max function

[siddharthsoam]

Hi

I want to apply a condition in max function.

Max({<[Fiscal Year]='FY16'>}( mid(Quarters,2)))

Ideally it should give me 4, but its not returning any value.

[siddharthsoam]

Hi Pradosh,

Quarters  Expected Output 

Q1                    Q4(FY16)

Q2                    Q1

Q3                    Q2

Q4                    Q3

For multiple selection of quarters it should return multiple respective values

[shraddha_g]

can you share some sample data?

[pradosh_thakur]

Try something like this

=Concat(DISTINCT pick(MATCH(Quarters,'Q1','Q2','Q3','Q4'),'Q4(2016)','Q1','Q2','Q3'),',')

[siddharthsoam]

Hi Pradosh,

I want that when my quarter = Q1 & fiscal year FY17, my KPI should return the Q4 of year FY16

[shraddha_g]

It will be helpful if you can provide some sample data available in field Month  and What is the first month of the year?

[siddharthsoam]

Hi Shraddha

PFA the qvf my month starts from April and thereon.

[shraddha_g]

PFA .

I have done calculation in FrontEnd.

Same thing can be done in Script.

I didn't have Excel but I have added code in Script. Only Set analysis part is remaining. I have mentioned that in script.

[siddharthsoam]

I want that my fiscal year column should change to 2016 when I select Q1 of 2017 in previous quarter

[shraddha_g]

It will..

Share me sample excel.. Will give you that in Script

[siddharthsoam]

Shraddha  i am unable to attach the excel in the editor