75 Replies Latest reply: Nov 20, 2017 6:49 AM by siddharth soam

# Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

Hi,

You have missed that curly bracket, just add like below try again. tks

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

• ###### Re: Can we put condition in max function

Sorry Devarasu forgot that but I am still facing another problem. The following max condiotion is not working in the expression with concat

Concat(Distinct 'Q' & if( (( mid(Quarters,2))-1)>0 and [Fiscal Year]='FY17', ( mid(Quarters,2))-1, (Max({<[Fiscal Year]={'FY16'}>}(mid(Quarters,2))))),',')

Its showing nested aggregation not allowed.

• ###### Re: Can we put condition in max function

what are you trying to do here?

• ###### Re: Can we put condition in max function

I have my quarters as Q1,Q2,Q3,Q4 so I am generating previous quarters for the respective selection. Mid function is used to fetch 1 from Q1. For Q0 it should show me quarter 4 or the maximum quarter for year FY16

• ###### Re: Can we put condition in max function

do you have access to script?and do you have date field in data model?

• ###### Re: Can we put condition in max function

Yes I have access to script , I don't have any date field in data model

• ###### Re: Can we put condition in max function

MonthField?

• ###### Re: Can we put condition in max function

Yes I have a month field

• ###### Re: Can we put condition in max function

can you share some sample data?

• ###### Re: Can we put condition in max function

Hi sidharth

do you want only the previous quarter for the date selected in a text box or something. This doesn't need to be this complicated. Can you share the input and expected output.

regards

• ###### Re: Can we put condition in max function

Quarters  Expected Output

Q1                    Q4(FY16)

Q2                    Q1

Q3                    Q2

Q4                    Q3

For multiple selection of quarters it should return multiple respective values

• ###### Re: Can we put condition in max function

Try something like this

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

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

PFA the qvf my month starts from April and thereon.

• ###### Re: Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

It will..

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

• ###### Re: Can we put condition in max function

Shraddha  i am unable to attach the excel in the editor

• ###### Re: Can we put condition in max function

have a look at the attached.

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

Attachment is missing. Could you please resend it.

• ###### Re: Can we put condition in max function

Also provide me excel which you have used for this data

• ###### Re: Can we put condition in max function

Hi

PFA for app file and base data

• ###### Re: Can we put condition in max function

PFA. (Refer 2nd Sheet)

Also I have modified fields in Backend.

• ###### Re: Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

Use OldFiscalYear Field for that format.

And find attached app for Same logic for Month

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

What is this part used for in the expression?

• ###### Re: Can we put condition in max function

Nulify the selections in order to avoid its effect and to make expression work according to Set analysis part

• ###### Re: Can we put condition in max function

I am facing little discrepancy in the data. When I select quarter 2, it is showing me total service_rev as 22.62 in previous tab i.e. data for Q1, but when I select quarter1 in  filter it shows me 23.33 in the recent tab.

Filter selection quarter-Q1 Fiscal Year-2017

Filter selection quarter-Q2 Fiscal Year-2017

• ###### Re: Can we put condition in max function

I can see that table are referring to Recent Months & Previous Months

You need to check table with Recent Quarter and Previous Quarter

• ###### Re: Can we put condition in max function

PFA

• ###### Re: Can we put condition in max function

Actually I want a like to like comparison for example if  only i select  fiscal year, it should show me the previous fiscal year under the previous tab. If I select a quarter suppose Q2 it shuld show me Q1 under previous. Likewise for months ,but if I select quarter and months simultaneously example Q2 and August , then data for Q1 and May should be reflected under previous.

This functionality should work on multiple selections as well.

• ###### Re: Can we put condition in max function

It will work that way only

• ###### Re: Can we put condition in max function

Can we use mid function in max ex-

max({<fiscal_year={'FY'&(mid(fiscal_year,2)-1)}>}quarter)

I am getting an error doing so

• ###### Re: Can we put condition in max function

Try

max({<fiscal_year={"\$(=Maxstring(fiscal_year))"}>}quarter)

• ###### Re: Can we put condition in max function

This expression is working well, but I cannot use it in the following condition. I have embedded the expression in max_quarter.

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

• ###### Re: Can we put condition in max function

What are you trying to achieve here?

• ###### Re: Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

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?

• ###### Re: Can we put condition in max function

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.

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

Regards

Siddharth

• ###### Re: Can we put condition in max function

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?

• ###### Re: Can we put condition in max function

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.

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

PFA

• ###### Re: Can we put condition in max function

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

• ###### Re: Can we put condition in max function

Yes it is possible

• ###### Re: Can we put condition in max function

PFA

• ###### Re: Can we put condition in max function

Shraddha I want the same functionality but for previous the quarter and month should be computed within the same measure. I am facing conflict in assigning multiple values for quarter and month to the monthstart field.

For previous month it is

Sum({<MonthStart = P(PreviousMonthStart),Quarter=,Quarters=,[Fiscal Year]=,OldFiscalYear=,Months=,MonthYear=>}[Service Rev])

For previous quarters its is-

Sum({<QuarterStart = p(PreviousQuarterStart),Quarter=,Quarters=,[Fiscal Year]=,OldFiscalYear=,MonthStart=>}[Service Rev])

I want to combine both the expression under the tab previous.

Thanks for coping with me . Really appreciate ur efforts.

• ###### Re: Can we put condition in max function

not so sure about combining both into one.

Lets call Sunny. He might help in combining these expressions

stalwar1

• ###### Re: Can we put condition in max function

can you elaborate the use of MonthStart you created , what value does it have

• ###### Re: Can we put condition in max function

I always prefer set analysis at Date level.if you are selecting 'Jan' and if you are having 3 years of data and you are using max(month) in Set analysis, It will always combine all 3 years of Jan data and will give result.

To specify Year you will again have to add 2nd condition of Year in set analysis.

I find it more comfortable in using dates over other parameters.

Its easier to put conditions on Date instead of these Multiple parameter conditions

• ###### Re: Can we put condition in max function

Shraddha thanks for calling out my name, but this seems like a rather long discussion thread... is my help still needed here? I thought of asking this before I go through this humongous thread

• ###### Re: Can we put condition in max function

We need your help at point where we need to combine 2 expression where we are using those for previous quarter and previous months

Individually it works fine.

Nut now their requirement is both expression should be combined into one..

I am not able to write it in d way that after selection of any quarter or month how it will work for previous month and previous quarter in one expression only.

I am getting all wrong result.

So i thought you might help in this. Whether it is possible to do..

• ###### Re: Can we put condition in max function

Would you be able to share the version of the file where the two expressions work individually?

• ###### Re: Can we put condition in max function

Found out... in the application... I am seeing two sheets, which sheet and chart are we working with . Still trying to narrow down on what I should be focusing on

• ###### Re: Can we put condition in max function

My new sheet(1)

There 2 pivots are there..

Both are working.

One is based on max(quarterStart) or max(MonthStart).

Another is based on p()

• ###### Re: Can we put condition in max function

All the functionalities are working well Shraddha but when i select months the previous quarter measure goes blank, so my sample output should be

Sample 1.

Current Quarter Selection        Mon

Q2                                             Aug

Should show me data for Q1 May under previous.

Sample 2

Current Quarter Selection        Mon

Q3                                             Oct,

Q2                                             Aug

Should show me data for Q2 July and Q1 May  under previous.

Only single selections are allowed for months column.

• ###### Re: Can we put condition in max function

Can you update the expression for previous months you are using to -3 ,like for mar it should show January..

• ###### Re: Can we put condition in max function

Thanks for coping with me Shraddha

Appreciate it

• ###### Re: Can we put condition in max function

So, I have been brought a little up to speed by Shraddha, now I would like you to tell me what exactly do you mean when you say you want to combine them? Can you tell me what you envision the numbers to look like (with and without selections) when the two charts are combined into one?

• ###### Re: Can we put condition in max function

Hi Sunny thanks for coming in,

there has been a little change in requirement , in a nutshell ,now i want multiple selections for quarter and single selection for month.

If the user selects a particular quarter for ex Q2 data of Q1 should be shown. If he selects quarter->Q2 and month->Aug data of Quarter->Q1 and month->May should be shown under previous header. Previous quarter when we select Q1 should return Q4 quarter data for the previous year.

These functionalities should work for multiple selections as well in quarters.

Now Shraddha has helped me a lot in achieving this , and i am able to get previous quarter data over multiple selections. I want to basically club the multiple quarter selections condition and month selection condition in a single sum expression.

• ###### Re: Can we put condition in max function

What you are saying make sense, but I am not sure I can translate all this into numbers... What I am looking for is expected output based on few different selections. I know how the numbers look in two different charts, what I am not sure is that what you expect to see when the expressions are combined? Just Exp1 + Exp2? Or something else? Does that make sense?

• ###### Re: Can we put condition in max function

Hi Sunny,

Now its working fine if I select quarters and months simultaneously.

I just combined previous month and quarter selection in if statement. My measure is as follows.

The only thing I want is that instead of monthstart showing me the -1 month(for ex feb for march) it should return me -3 month(for ex jan for march)

• ###### Re: Can we put condition in max function

I am not sure, but this part of your expression should already be doing this?

For March, it should give you Jan... but do you also want April to give you Feb?

• ###### Re: Can we put condition in max function

Yea Sunny exactly.

My sample output should be

Sample 1.

Current Quarter Selection        Mon

Q2                                             Aug

Should show me data for Q1 May under previous.

Sample 2

Current Quarter Selection        Mon

Q3                                             Oct,

Q2                                             Aug

Should show me data for Q2 July and Q1 May  under previous.

Only single selections are allowed for months column.

• ###### Re: Can we put condition in max function

May be create a new field like this

addmonths(Date(Date#(Months &' ' &if(wildmatch(Months,'Jan','Feb','Mar'),[Fiscal Year]+1,[Fiscal Year]),'MMM YYYY')),-2) as Previous2MonthStart,

And then instead of using MonthStart = p(QuarterStart), use MonthStart = p(Previous2MonthStart)

• ###### Re: Can we put condition in max function

Any update on this?

• ###### Re: Can we put condition in max function

Thanks buddy, your expression is working perfect for showing previous quarters data. For now my requirement is limited to single selection in months filter.