Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Highlighted
divyanshi
New Contributor II

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:
Load
"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.

8 Replies
micheledenardi
Valued Contributor

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:

Load

    Field1

    Max(Quarters) as MaxQuarter

resident ResidentTab

     group by Field1;

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

drop table TabMaxQuarter;


tab1:
Load
"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";

divyanshi
New Contributor II

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

micheledenardi
Valued Contributor

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 ?

divyanshi
New Contributor II

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.

divyanshi
New Contributor II

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.

micheledenardi
Valued Contributor

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

Can you share some example data and your code ?

divyanshi
New Contributor II

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

tab1:

Load

"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).

micheledenardi
Valued Contributor

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 ?