8 Replies Latest reply: Oct 31, 2017 3:29 AM by Michele De Nardi

# want to use max function in where clause of load editor in qliksense?

Hi,

I want to calculate the data of latest Quarter of the year.

Quarter is the column in the table FY.

tab1:
"Customer Group",

Text(if(Sum("Total Revenue")<=0.25,'Tail Account (Rev< 1 Mn)',
if(Sum("Total Revenue")>=1.2,'Large Account (Rev> 5 Mn)',
If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<=1.2,'Middle Account (Rev 1-5 Mn)')))) as account
Where Quarters=Max(Quarters)

Resident FY
Group by "Customer Group";

Using max(Quarters ) in where clause gives error.

Kindly help.

• ###### Re: want to use max function in where clause of load editor in qliksense?

I suggest to store Max(Quarters) on a variable by using Peek() function and then use the variable on your where clause, so:

```TabMaxQuarter:
Field1
Max(Quarters) as MaxQuarter
resident ResidentTab
group by Field1;

let vMaxQuarter=Peek('TabMaxQuarter',0,'MaxQuarter');
drop table TabMaxQuarter;

tab1:
"Customer Group",
Text(if(Sum("Total Revenue")<=0.25,'Tail Account (Rev< 1 Mn)',
if(Sum("Total Revenue")>=1.2,'Large Account (Rev> 5 Mn)',
If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<=1.2,'Middle Account (Rev 1-5 Mn)')))) as account
Resident FY
Where Quarters='\$(vMaxQuarter)'
Group by "Customer Group";

```
• ###### Re: want to use max function in where clause of load editor in qliksense?

After applying your logic, now I m facing problem regarding account type. i.e

The below line is not working now.

If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<=1.2,'Middle Account (Rev 1-5 Mn)')))) as account

Snapshot attached

• ###### Re: want to use max function in where clause of load editor in qliksense?

Can you explain which is the actual situation and the expected result ?

• ###### Re: want to use max function in where clause of load editor in qliksense?

The account types are my column under which there different measures.

Now after calculating the max value of quarter its reflecting in the column tab as shown in snapshot.

I just want to calculate the max value in order to use it the measure.

• ###### Re: want to use max function in where clause of load editor in qliksense?

My requirement is in snapshot.

I just need to calculate the max value of quarter in order to represent the data as per max quarter value i.e the data should be represented corresponding to the max value of the respective year.

The issue I m facing after applying your logic is that my column Account Type are not working and the value of max quarter is reflecting the column tab.

• ###### Re: want to use max function in where clause of load editor in qliksense?

Can you share some example data and your code ?

• ###### Re: want to use max function in where clause of load editor in qliksense?

tab1:

"Customer Group",

Text(if(Sum("Total Revenue")<=0.25,'Tail Account (Rev< 1 Mn)',

if(Sum("Total Revenue")>=1.2,'Large Account (Rev> 5 Mn)',

If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<=1.2,'Middle Account (Rev 1-5 Mn)')))) as account

Resident FY18

Group by "Customer Group";

This is the expression I am using to create column account(Tail, Middle & large).

• ###### Re: want to use max function in where clause of load editor in qliksense?

Ok, probably the problem is on the customer group, can you give us some representative example data ?