7 Replies Latest reply: Sep 9, 2015 7:26 PM by arjun rao

# YTD expression with if condition

Hello All,

I am trying to develop a chart for all available activities.

The chart has to display YTD data. The required formula for expression is : (Total Cost of Activity) / (Exchange Rate).

So, The YTD formula is:

(sum({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=,Type=>}TotalCostofActivity))

/ (Avg({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=>}EXCHANGE_RATE)

I have a situation where, some values of Total cost of Activity are null and some values of Exchange rate are null.

The chart displays wrong total amounts.

Can you all Please help me to develop dual if condition for the above mentioned YTD formula such that, zero value is displayed if either total cost of activity or exchange rate is null.

• ###### Re: YTD expression with if condition

may be try

(sum({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=,Type=>} if(len(trim(TotalCostofActivity))=0,0,TotalCostofActivity)))

/

(Avg({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=>} if(len(trim(EXCHANGE_RATE))=0,0,EXCHANGE_RATE))

hth

Sasi

• ###### Re: YTD expression with if condition

Hi,

You can try this.

(sum({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=,Type=>} if(len(trim(TotalCostofActivity))=0,0,TotalCostofActivity)))

/

(Avg({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=>} if(len(trim(EXCHANGE_RATE))=0,0,EXCHANGE_RATE))

• ###### Re: YTD expression with if condition

Without a sample qvw, I guess that the values are coming out as null, and not as missing. Then use:

=Alt((sum({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=,Type=>}TotalCostofActivity))

/ (Avg({<YEAR={\$(vMaxYear)},Start_Date={"<=\$(vMaxDate)"},Month1=>}EXCHANGE_RATE), 0)

• ###### Re: YTD expression with if condition

hi

have you managed to solve your issue?

Sasi

• ###### Re: YTD expression with if condition

Hi sasidhar,

I am still working on the issue.I don't have a solution as yet.

Appreciate all of you for your suggested solutions.

I will update once I find a solution.

• ###### Re: YTD expression with if condition

give this a try,

create two new variable:

vYTDStart =Min({<Year={"\$(vMaxYear)"}, Month=>} date)

vYTDEnd =MAx({<Year={"\$(vMaxYear)"}>} date)

Expression:

Sum({<Year=,Type=,Month=, date={'>=\$(vYTDStart)<=\$(vYTDEnd)'}>}TotalCostofActivity) /

/ Avg({<Year=,Month=, date={'>=\$(vYTDStart)<=\$(vYTDEnd)'}>}EXCHANGE_RATE)

Bon