How can i apply condition in a group by expression.

[siddharthsoam]

Hi,

I want only those customers to come with revenue< 1mn corresponding to my previous_fiscal year i.e. 1-max(fiscal year). But when I apply the following condition-

Temp:

Load

((Max(Mid("Fiscal Year",3)))-1) as Prevfiscalyear

Resident PNL;

Let vPrevfiscalyear =Peek('Prevfiscalyear');

tab2:

Load

"Fiscal Year",

Quarters,

"Customer ",

leaders,

if(sum(Revenue)<1,'Tail Accounts(Rev<1Mn)',

) as Accounts

Resident PNL

where "Fiscal Year"=$(vPrevfiscalyear)

Group by "Fiscal Year","Customer ="leaders;

I cannot see any customer corresponding to FY18 selection at frontend

[bramkn]

The following will not work: Group by "Fiscal Year","Customer ="leaders;

[krishna_2644]

Share a mock - up or seee below for suggestions -

Temp:

Load

((Max(Mid("Fiscal Year",3)))-1) as Prevfiscalyear

Resident PNL;

Let vPrevfiscalyear =Peek('Prevfiscalyear');  //Table name not mentioned eg : peek('Prevfiscalyear',0,minmax)

tab2:

Load

"Fiscal Year",

Quarters,

"Customer ",

leaders,

if(sum(Revenue)<1,'Tail Accounts(Rev<1Mn)',

) as Accounts   // if (Rev<1Mn is condition, then this if() statement will not work - syntax errors//

Resident PNL

where "Fiscal Year"=$(vPrevfiscalyear)

Group by "Fiscal Year","Customer ="leaders; //group by will not work as the number of load fields not equals to group by fields.Also you cannot have operators in a group by//