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.

[shraddha_g]

What are you trying to achieve here?

[siddharthsoam]

I have created a variable using this expression, i.e.

max({<fiscal_year={"$(=Maxstring(fiscal_year))"}>}quarter) named Max_quarter.

Now I want to use this variable Max_Quarter inside CONCAT() function along with if condition i.e.

concat( distinct if(mid(Quarters,2)=1,$(maxQuarter)),',')

But not getting the result as required.

[shraddha_g]

You are using wrong variable name

it should be $(Max_quarter) in your expression.

But why you are using Concat() here.

It will return you 1 value only.

What should be your expected outpu?

[siddharthsoam]

Sorry I typed the variable name wrong while replying you,

We want that whenever we select Quarter Q1 even if the multiple quarters are selected, then it should get replaced by Q4.

- In order to get Quarter value Q1 I m using if() condition i.e. if(mid(Quarters,2)=1

- For multiple Quarter Selections I m using CONCAT() function.

- In order to replace Q1 with Q4 I m using variable Max_Quarter. which makes my expression as

concat( distinct if(mid(Quarters,2)=1,$(Max_Quarter)),',')

Current Quarter                Previous Quarter

Q4                                      Q3

Q3                                      Q2

Q2                                      Q1

Q1                                      Q0

My Requirement is that this Q0 should be replaced by Q4 of the previous year i.e.

Current Quarter                  Previous Quarter

Q4                                      Q3

Q3                                      Q2

Q2                                      Q1

Q1                                      Q4

NOTE: Previous year logic has already been implemented in Max_Quarter variable.

Please suggest changes in already build expression i.e. in

concat( distinct if(mid(Quarters,2)=1,$(Max_Quarter)),',')

Regards

Siddharth

[shraddha_g]

I guess this requirement is already provided in one of the qvf I had attached.

PFA image which shows Current quarter and Previous Quarter

Do you want it to be calculated and stored in a field?

[siddharthsoam]

Totally agree but is it possible to manipulate this expression in doing so

concat( distinct if(mid(Quarters,2)=1,$(Max_Quarter)),',')

Yes I want to manipulate my sum expression according to this condition.

[shraddha_g]

I doubt.

But why do you want to use Concat().

When you can directly get previous Quarter from QuarterStart Date then you can use it in set analysis

[shraddha_g]

PFA

[pooja821]

Hi Shraddha can we merge both months and quarters logic in the same pivot and rename year as FY16 and FY17.

Thanks in advance  

[shraddha_g]

Yes it is possible