Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- Basis Points Calculation

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

lmychajluk

New Contributor

05-29-2018
10:51 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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!

140 Views

13 Replies

stalwar1

MVP

05-29-2018
10:59 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Is it possible to share a sample to show the issue?

shansundar

Contributor

05-29-2018
11:12 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Its a problem with BODMAS. When you create the variable include some open/close parenthesis in your expression and try again, like

vFm_TotDaysInLastFY = (YearEnd( Today(),-1 ,10)-YearStart( Today(),-1 ,10) )

because when you replace this expression YearStart( Today(),-1 ,10) part of your variable will get multiplied with 10000 first so you're getting negative values.

Please try once and let us know.

Thanks,

Shan S

lmychajluk

New Contributor

05-29-2018
11:57 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thanks for the reply.

I think I see what you're saying, but I'm going on the assumption that a variable used in an expression is evaluated first. Is this not the case? In my example, wouldn't vFm_TotDaysInLastFY already be evaluated (to 365, for example). Then, in the 'main' formula, the variable would just be treated the same as any constant?

I tried wrapping the values in the variables in parenthesis as you suggested, but didn't see any changes. I've also been trying to wrap each variable in parenthesis within my formulas to force them to evaluate first(?), but that doesn't seem to work either.

For comparison, my Cur YTD BPS formula seems to work fine:

(($(vFm_CurFY_Revenue))/($(vFm_CurFY_YTD_ANA))/($(vFm_DaysInCurFY)))*365*10000

But when I substitute in the variables for 'last FY' into the same formula,it doesn't seem to evaluate (shows '-').

(($(vFm_LastFY_YTD_Revenue))/($(vFm_LastFY_YTD_ANA))/($(vFm_DaysInLastFY_YTD)))*365*1000

Sorry, I cannot post any data, as it's somewhat sensitive.

Thanks again.

marcginqo

New Contributor III

05-29-2018
02:00 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi Lee,

It all has to do with placing the brackets on the correct position.

Based on you expression the calculation is as follow.

First is calculates this : ($(vFm_LastFY_YTD_Revenue))

Then it divides this value by ($(vFm_LastFY_YTD_ANA))

Next it divides the value ($(vFm_LastFY_YTD_Revenue))/($(vFm_LastFY_YTD_ANA)) by ($(vFm_DaysInLastFY_YTD))

The result of this is multiplied by 365 and then by 1000.

So by placing the brackets in different positions the outcome will be different.

So for example if you want the value of ($(vFm_LastFY_YTD_Revenue)) divide by the value of ($(vFm_LastFY_YTD_ANA))/($(vFm_DaysInLastFY_YTD)) you need to move brackets around

(($(vFm_LastFY_YTD_Revenue))/ **(** ($(vFm_LastFY_YTD_ANA))/($(vFm_DaysInLastFY_YTD)) **) **)*365*1000

In this case it will divide the value of ($(vFm_LastFY_YTD_Revenue)) by the value of **(** ($(vFm_LastFY_YTD_ANA))/($(vFm_DaysInLastFY_YTD)) **) **and eventually it will multiply the outcome with 365 and then multiply by 1000

Look at these examples, they look the same by are different in position of brackets.

( ( 1000 ) / ( 100 ) / ( 10 ) ) * 365 * 1000 = 365.000

( ( 1000 ) / **(** (100 ) / ( 10 ) **)** ) * 365 * 1000 = 36.500.000

Hope this helps.

lmychajluk

New Contributor

05-29-2018
02:56 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Thanks, Marc. I just don't think it's an order of operations issue, though maybe I'm not understanding how Qlik is evaluating the expression? Or, maybe I'm not making myself clear. Let me try again.

This formula gives me a result (I'm not positve it's the *correct *result yet, but it is ** a** result. Let's just assume it's correct for the sake of this example):

($(vFm_LastFY_YTD_Revenue))/($(vFm_LastFY_YTD_ANA))*(** 365***10000)/$(vFm_DaysInLastFY_YTD)

In this version of the formula, I'm simply trying to replace the constant of 365 w/ the variable $(vFm_TotDaysInLastFY):

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

The formulas are identical except for what's in bold/italics. Yet, the first formula evaluates to a value (47.13), while the second returns '-'. Regardless of the value that is returned, shouldn't both formulas return the same value if *vFm_TotDaysInLastFY **is 365? (*If I plug ** vFm_TotDaysInLastFY **into a measure, I get the value of 365.) I would think I should get the same value returned from the top formula? But, it seems like something is being evaluated differently when I try to use the variable.

I feel like I'm missing something fundamental here...

marcginqo

New Contributor III

05-29-2018
03:05 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Lee,

Try to put the variable between brackets just to make sure.

So:

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

Qlik evaluates the expression/variables in total. So it will try to change the variables to the expression in the variable. Could be that this is causing an issue with brackets.

Marc

lmychajluk

New Contributor

05-29-2018
03:47 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Even w/ the extra set of parenthesis as you indicated, it still evaluates to '-'. I've tried that as well as using '=' in a few variations (as I've seen indicated in some posts here):

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

and

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

marcginqo

New Contributor III

05-29-2018
03:54 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

What is the definition of your variable?

Could be that you can use just the name of the variable. So not $(vFm_TotDaysInLastFY) but just vFm_TotDaysInLastFY.

Try:

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

lmychajluk

New Contributor

05-29-2018
04:14 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

That didn't seem to work either. I thought variables had to be $ expanded?

*vFm_TotDaysInLastFY *is defined as:

($(vLastFiscalYearEnd)-$(vLastFiscalYearStart))

*vLastFiscalYearEnd* is:

YearEnd( Today(),-1 ,10)

*vLastFiscalYearStart* is:

YearStart( Today(),-1,10)

Note that these are defined as variables in the App, not in the load script. I can move them to the load script (would be more efficient), but put them in the app because data would only be loaded monthly.