# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Contributor

## Basis Points Calculation

I'm trying to write a formula, using variables, to calculate the basis points of an investment for the previous year.

The basic formula is (Rvenue/(Average Net Assets)/(Days in Period)/365*10000

The formula I"m using (in a variable) that seems to return a (not yet validated, but seems close) value is:

(\$(vFm_LastFY_YTD_Revenue))/(\$(vFm_LastFY_YTD_ANA))*3650000.0/\$(vFm_DaysInLastFY_YTD)

(If I change the order of operations and put the 3650000 at the end, I get a '1' as the result, which I'm also not quite clear on. Why would order of ops matter here? Also, removing or adding additional parenthesis to control the order of operations seems to break the formula.)

All the values for the individual variables seem correct, and return positive numbers.

However, I've been asked to account for leap years, so I want to substitute the '365' with a variable. That variable (vFm_TotDaysInLastFY) is basically defined as YearEnd( Today(),-1 ,10)-YearStart( Today(),-1 ,10) and returns 365 for the last FY (again, correct).

However, when I try to substitute the variable vFm_TotDaysInLastFY into the original formula, I get a slightly different value from the original formula, and it is also negated:

(\$(vFm_LastFY_YTD_Revenue))/(\$(vFm_LastFY_YTD_ANA))*\$(vFm_TotDaysInLastFY)*10000.0/\$(vFm_DaysInLastFY_YTD)

Since all the individual values are positive, I don't see how I'm getting a negative number here.

I'm missing something, I'm just not exactly sure what....

Can anyone point me at what I'm doing wrong? Thanks!

13 Replies
Partner

Lee,

those formulas seem OK. And you are right, if you use it this way you must use the \$ expanded. If it would be a constant you don't need to.

What is the definition of vFm_DaysInLastFY_YTD?

Contributor

This one is a bit more complex.

vFm_DaysInLastFY_YTD:

(((Max({\$<Asset_FiscalYear={'\$(vLastFiscalYear)'},Asset_FiscalMonth={'\$(vMaxFiscalMonth)'}>}[Asset_Date]))-(Min({\$<Asset_FiscalYear={'\$(vLastFiscalYear)'}>}[Asset_Date])))+1)

Asset_FiscalYear and Asset_FiscalMonth are evaluated in script as part of my Calendar, all based off of Asset_Date. vLastFiscalYear and vMaxFiscalMonth are also evaluated in the load script. All of these values, as well as the value for vFm_DaysInLastFY_YTD itself, all seem to show the correct values when I pop them into a measure on thier own. (vFm_DaysInLastFY_YTD was checked using DAYS function in Excel.)

Partner

Lee,

Try this:

(\$(vFm_LastFY_YTD_Revenue)) / (\$(vFm_LastFY_YTD_ANA)) * ( ( \$(vFm_TotDaysInLastFY) ) * 10000 )  /  ( \$(vFm_DaysInLastFY_YTD) )

So put the last variable also in its own brackets.

Contributor

Still no. Returns '-'. I think I tried some variation of that as well (putting all the variables in thier own parenthesis.)