Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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))
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)
hi
have you managed to solve your issue?
Sasi
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.
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