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 · Accepted solution]

have a look at the attached.

[siddharthsoam]

Hi Shraddha,

Your expression for computing previous quarter is working well for columns but i am facing problem in order to compute it in a measure. I have attached the app file. My objective is to find the sum of service_rev for the previous quarter even on multiple selections.

So if i select Q1,Q2 for the fiscal year FY17, it should show me the sum of service_rev for the quarters Q1(Fiscal Year-FY17),Q4(Fiscal Year- FY-16) under the previous quarter tab in pivot.

Thanks in advance new to Qlik

[shraddha_g]

Attachment is missing. Could you please resend it.

[shraddha_g]

Also provide me excel which you have used for this data

[siddharthsoam]

Hi

PFA for app file and base data

[shraddha_g]

PFA. (Refer 2nd Sheet)

Also I have modified fields in Backend.

P.S - Before Reloading please change Data connection Path

[siddharthsoam]

Hi Shraddha,

Thanks for your help appreciate that .. can  you tell me if I want to show year in the format FY17 and FY18 how can I show in the view. Also if I want to extend the functionality to months as well i.e. data change on selection of months.

[shraddha_g]

Use OldFiscalYear Field for that format.

And find attached app for Same logic for Month

[siddharthsoam]

This was helpful, thanks once again. can you please explain what you did in the expression

[shraddha_g]

From your data I created Date Field (Month Start Date / Quarter Start Date) for Text Month / Quarter column.

And in Set analysis instead of referring to Text field I used Date Field for simple expression.

Max(MonthStart) will refer to Recent (Max) Month from Data. According to data you provided it will give you Aug 2017.

When you select Jan from your 'Months' field Max(MonthStart) will return Jan 2017 being the max in Jan 2016 & jan 2017 and addmonths(Max(MonthStart),-1) will return Dec 2016.

if you select FY16 along with Months as Jan then Max(MonthStart) will return Jan 2016 and addmonths(Max(MonthStart),-1) will return Dec 2015.

All these calcultions are done on Monthstart field Since it is a date and it represent both columns 'Months' & 'FiscalYear'

Same explanation is applicable for Quarter.