4 Replies Latest reply on Aug 20, 2018 4:07 AM by pavan kumar

# Different formulas for Different Quarter

Hi,

This is my data

if it is 2018 Q1 or 2018 Q2 or 2018 Q3 then sum(Amount) + lookup(sum(Amount),-1)*.5

if it is 2018 Q4 then sum(Amount)+lookup(sum(Amount),1)*.1

Same thing for other years 2019, 2020 (This is sample data .so there is no data for 2019 and 2020)

Its giving me an error when i am using Contains() function to find out Q4.

Quarter  is in String Format.

Any Help.

Thanks

Pavan Kumar

• ###### 1. Re: Different formulas for Different Quarter

Hi Pavan Kumar,

You are getting error 'cannot mix aggregate and non aggregate comparisons or results in IF expressions' in your formula. I copied the the same formula and revised it with function attr to resolve it. Also, used function zn to get value for 2018 Q1 as there is NULL value for Amount in previous quarter,

Please check the revised workbook attached. Let me know if it works for you.

Vijay

1 of 1 people found this helpful
• ###### 2. Re: Different formulas for Different Quarter

Hi Vijay ,

Thanks

for the reply , its working now.

There is another one i need to calculate.

could you plz check Calculation1 field which is in the view, i am not getting any output.

plz find attached work book

• ###### 3. Re: Different formulas for Different Quarter

Hi Pavan,

Lookup is table calculation and the sum is also aggregated function  in order to align your calculation change your formula like below

if ATTR(CONTAINS([Quarter],"Q4")) then

SUM([Amount])+ZN(LOOKUP(SUM([Amount]),1))*.1

else

SUM([Amount])+ZN(LOOKUP(SUM([Amount]),-1))*.5

end

ATTR - Will convert your dimension to the aggregated value it will help to resolve your issue

Hope this helps kindly mark this answer as correct and helpful so that it will be helpful to others

BR,

NB

1 of 1 people found this helpful
• ###### 4. Re: Different formulas for Different Quarter

for the reply , its working now.

There is another one i need to calculate.

could you plz check Calculation1 field which is in the view, i am not getting any output.

for "term 1" i want Term1 Amount *.5 +Term 2 Amount

for "term2" i want Term1 Amount*.5*3+Term2 Amount*0

plz find attached work book