YTD expression with if condition

[#1]

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.

[sasiparupudi1]

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

[#3]

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

[jonathandienst]

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)

[sasiparupudi1]

hi

have you managed to solve your issue?

Sasi

[#6]

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.

[Anonymous]

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

[qlikviewwizard]

HI,

Please check these two links:

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods