topic Re: YTD expression with if condition in QlikView

YTD expression with if condition

Thu, 03 Sep 2015 18:33:59 GMT

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

sasiparupudi1 — Thu, 03 Sep 2015 20:10:39 GMT

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

Mon, 07 Sep 2015 11:50:58 GMT

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

jonathandienst — Mon, 07 Sep 2015 12:16:32 GMT

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

sasiparupudi1 — Wed, 09 Sep 2015 18:50:58 GMT

have you managed to solve your issue?

Sasi

Re: YTD expression with if condition

Wed, 09 Sep 2015 19:05:12 GMT

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

Anonymous — Wed, 09 Sep 2015 23:08:53 GMT

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

Re: YTD expression with if condition

qlikviewwizard — Wed, 09 Sep 2015 23:26:11 GMT

HI,

Please check these two links:

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods