
Re: Basis Points Calculation
Sunny Talwar May 29, 2018 10:59 AM (in response to Lee Mychajluk)Is it possible to share a sample to show the issue?

Re: Basis Points Calculation
Shanmuga Subbiah May 29, 2018 11:12 AM (in response to Lee Mychajluk)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

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 11:57 AM (in response to Shanmuga Subbiah )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*10000But 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.

Re: Basis Points Calculation
Marc Algera May 29, 2018 2:00 PM (in response to Lee Mychajluk)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.

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 2:56 PM (in response to Marc Algera )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...

Re: Basis Points Calculation
Marc Algera May 29, 2018 3:05 PM (in response to Lee Mychajluk)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

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 3:47 PM (in response to Marc Algera )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)

Re: Basis Points Calculation
Marc Algera May 29, 2018 3:54 PM (in response to Lee Mychajluk)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)

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 4:14 PM (in response to Marc Algera )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.

Re: Basis Points Calculation
Marc Algera May 29, 2018 4:41 PM (in response to Lee Mychajluk)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?

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 4:57 PM (in response to Marc Algera )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.)

Re: Basis Points Calculation
Marc Algera May 29, 2018 5:02 PM (in response to Lee Mychajluk)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.

Re: Basis Points Calculation
Lee Mychajluk May 29, 2018 5:23 PM (in response to Marc Algera )Still no. Returns ''. I think I tried some variation of that as well (putting all the variables in thier own parenthesis.)
Glad it's not just me.
This is the formula I'm using for the Current Year Value:
(($(vFm_CurFY_Revenue))/($(vFm_CurFY_YTD_ANA))/($(vFm_DaysInCurFY)))*365*10000
This one seems to work just fine, but if I swap the variables for last year's variables, it bombs.
Thanks for the help, but I'm calling it a day for now, and I have Jury Duty tomorrow. Hopefully come back to this on Thu.










