
1. Re: Layered YtD Calculation
Simon Runc Aug 6, 2015 10:36 AM (in response to Elodie Gbedan)hi Elodie,
Excellent, glad it's all working...btw I've branched this tread off to a new one.
Yes we should be able to achieve this. It depends on how your Week/Year is captured, but if I assume there is a Week and Year Column.
The first thing you want to do is to detect the last week in 2015. I have a trick for this, which I'll show you below is a series of formulas for explanation purposes.
Make a concatenation of your week and year colums and cast to integer (I'll call this 'YearWeek_Int')
INT(STR([Year]) + STR([Week])
So this will return 201529, 201530, 201531...etc.
Use an LoD, to get the MAX of this column (which will be 201531, in your example). I'll call this 'MaxWeekYear'
{MAX([YearWeek_Int])}
btw you don't need to use FIXED if you want a LoD for everything (i.e. not dependent on a field), just the {}
Now take the RIGHT 2 digits, which in your example will be 31, and cast as integer. I'll call this 'MaxWeek'
INT(RIGHT(STR([MaxWeekYear],2))
Now you have this, you can build your formula using this. such as
IIF([Week] = [MaxWeek], [POS $],NULL)
and then you can use <= so have YtD...etc.
I nest the whole lot in one calculated field so it's easier to maintain, but no difference leaving it as separate fields.
Hope this makes sense, as one you have this you can perform row level calcs and filters easily, but post back if not.

2. Re: Layered YtD Calculation
Elodie Gbedan Aug 10, 2015 8:34 AM (in response to Simon Runc)Hey Simon
As usual, I really appreciate the fact that you are looking into this.
I am at Step 3 where I wanted to create the following calculation, but I am having an error message
INT(RIGHT(STR([MaxWeekYear],2))
Basically, it is telling me that STR cannot be called with integer and if I meant boolean?

3. Re: Layered YtD Calculation
Simon Runc Aug 10, 2015 8:58 AM (in response to Elodie Gbedan)whoops, got my brackets in the wrong place!
Try
INT(RIGHT(STR([MaxWeekYear]),2))

4. Re: Layered YtD Calculation
Elodie Gbedan Aug 10, 2015 10:42 AM (in response to Simon Runc)Worked.
I will continue and let you know how the process went.

5. Re: Layered YtD Calculation
Elodie Gbedan Aug 10, 2015 12:37 PM (in response to Elodie Gbedan)Hi Simon
So I have been able to create the 'MazWeek' formula that allows to separate the last 2 digits,
Now I am trying to create the YTD
IFF([week]=[MaxWeek],[POS],NULL) and I m having the following error: ('Can't compare string and integer values'
My next step once I fixed this is to create a formula that would take the calculated field created last week ( the multiple)
and would basically say
IFF([Week]=[MAx Week],[Pos $]*average([Mult  PoS $  Part Dynamic])+[YTD], IF not NULL)
Thanks
I am also attaching the previous workbook
thx

6. Re: Layered YtD Calculation
Simon Runc Aug 11, 2015 2:21 AM (in response to Elodie Gbedan)...so looking at your screen shot, your week is a String type (you can see from the ABC next to the field). You can either change it to an integer in the field properties, or you can insert an INT into the formula (which Casts, as it's known, the Week field to an Integer for the formula). So would be
IIF(INT([week])=[MaxWeek],[POS],NULL)
You would then use this new field, which will only contain the data YtD in your Multiple Formulas.
I'm out and about today, but let me know if you're still having problems and I'll put the new fields into the attachment you've provided.

7. Re: Layered YtD Calculation
Elodie Gbedan Aug 11, 2015 6:42 AM (in response to Simon Runc)Thanks Simon. I will try this. The other question I have ( although I haven't tried yet) is the ability to average the Multiple
As I am getting to know tableau, it looks like the system does not allow you to average a calculated aggreagated field.
Remember, the multiple itself was a calculation.
My Projection formula would require to use the weekly POS * the average of the last 3 year (201220132014) multiple + YTD
Thanks

8. Re: Layered YtD Calculation
Elodie Gbedan Aug 11, 2015 1:28 PM (in response to Simon Runc)Hi Simon
While the following formula IIF(INT([week])=[MaxWeek],[POS],NULL) works, it doesn't give me an accurate results of YTD, ie sales from week 1 to week 31 in this case
Not sure what I missed here...
thx

9. Re: Layered YtD Calculation
Simon Runc Aug 12, 2015 3:52 AM (in response to Elodie Gbedan)hi Elodie,
I think this due to my understanding. I thought you wanted a ytd calculation that would let you look at YtD for each of the years (as a comparison), so currently this calculation includes all the years (just for Week 31).
I've added an extra calculation to give me MAX year, this is
{MAX(INT([Year]))}
I've then added an extra term to the IIF statement, so that it also restricts to the Max year too.
IIF(INT([Week])=[Max Week] AND INT([Year])=[Max Year] ,[Pos $],NULL)
on the 'final' formula. Yes you can't aggregate on an aggregate, so we have to use WINDOW_AVG to do this. This is the way we can Aggregate on an Aggregate, using Table Calculations.
I'm a little confused as to the current way this is set up. The IIF statements we've been building, are so we have a POS $ YtD field that only contains POS for YtD this year, so not sure why their is a further IIF Week = Max Week in here.
I'll have sometime later today, so I'll work up the formula from my understanding. The basic idea is that we can't use Filtering as we need the formula to have access to all years (so it can work out it's average), so we are using the IIF statements to create new measures which only contain data for the weeks we want. So I think I'll need to create one set based on 20122014, which we can average using WINDOW_AVG, and then another set (which we pretty much have) to do the YtD version. We can then use these 2 fields in our 'final' calculation. Just to be aware, as these are all built off table calculations, we will need Year and Week for all years and weeks in the Viz, so the table calculation can run over these levels.

10. Re: Layered YtD Calculation
Elodie Gbedan Aug 13, 2015 1:09 PM (in response to Simon Runc)Thanks again Simon.
1) The YTD formula was error free, however it didn't return any value when I select Year on row shelf and drag the measurre YTD on the row shelf.
Even when I filter by showing only Week 31 it wouldn't show me the YTD of each year from week 1 to week 31
I am able to show the YTD for each brand for each item for any item for all year, it would be great
Example: Brand X
show YTD 2006 ( week 131)
YTD 2007
YTD 2008
YTD 2009
and so on and so on....
2) the If max week number is just a way to tell the system to perform the calcatio using the max week of the time frame included in the data set. I agree that IF might not be the best approach. Essentially, what I want the formula to do is take the most recent week of sales * average of the multiple of the past 3 years (201220132014)+ YTD sales
In case the brand won't have any recent sales (most likely because it is a new brand) then I would ask the system to just put NULL.
I think I might be going too complex here. I am able to perform the calculation on excel, but in tableau seem to have a bit of hard time
Thanks again and your earliest convenience would work for me. No pressure.
Elodie

11. Re: Layered YtD Calculation
Simon Runc Aug 14, 2015 3:06 AM (in response to Elodie Gbedan)hi Elodie,
...as you can probably tell...time got away from me this week!
Yes these sort of very specific calculations are pretty complicated in Tableau, compared with Excel. They are 2 very different tools, so Tableau could create some extra 'Excel' like functionality for creating very specific calculations, but the tradeoff would be that the 80% of things which are very easy to do in Tableau, would become more complicated. In Excel you can reference any cell from any cell, so can break datastructuring rules to achieve almost any type of calculation. However the maintenance of these 'structure breaking' calculations can be a manual affair (in previous jobs I've often spent half of Monday manually 'correcting' this type of specific calculation when new data is added). The solutions to these problems in Tableau, although tricky to set up will work for all data.
Having said that I'm pretty sure I can create what you want (and from your latest comments I think I have a handle on this), but always with my caveat!...that the calculations we are performing need Year and Week in the detail, so the calculation can run over these weeks (i.e. there isn't a row level calculation we can perform that would populate the data, at row level and give the right result for any level, which you could then use as a usual 'additive' calculation, such as Sales Value). This means we need to set up the calculations specifically for each case (by Brand, By Product...etc.).
The main complication here, and what we can't achieve with Row Level/LoD calculations is the Running_Sum part. Just for future reference...this value could be calculated in the original data (which from my understanding, if done at product/year/week level would then work as an 'additive' field at any higher level). This wouldn't be too hard in Excel or SQL. I frequently have calculations (especially statistical ones) which although I could (in theory) calculate in Tableau, I end up getting calculated in the database, so just comes to me as a field. I often use Tableau to get the logic, but the restriction of what I can do (i.e. if I need week, as per your example, in the detail of the Viz it restricts, or complicates, any visualization I want to create as well as being potentially slow for the end user). Databases can run these calculation at night, when our Clients sleep, so if it take it 2 hours to calculate it's not a problem!
I'll have a play over the weekend/earlynextweek and (hopefully) get you a solution.
...Having worked on this for a bit now....Once we have the final version...I'd be very intrigued as to what this calculation is, what it's for, and what this measure shows/how it's used!! (if you are able to share that info)

12. Re: Layered YtD Calculation
Elodie Gbedan Aug 14, 2015 2:24 PM (in response to Simon Runc)Hey Simon
That's SUPER nice of you to look into this.
I agree with you that I would really be easier to get the calculations done in the database itself and bring it as a measure.
The multiple methodology is really a quick way to tell the exec team the estimated Projected POS Sales for each brand (Dollar volume) or item (Units volume)
If you are able to do that for each brand, you can therefore quote the estimate Projected POS the companies can expect to achieve.
The key is always add the forward projections to the YTD realized to be able to get the full year. If each week the amount you quote is more or less in the same ball park, that means that your estimate for the FY is pretty good and you have good chance that the year ending POS would reflect your sales.
The method however only works when you have at least:
3 years of data. for each item and brand ( in my case i have almost 9 years) but will use only the past 3 years as the industry has changed considerably for the past 3 years. Therefore you can have the multiple of each week relatively to Balance of year
Thanks again and really, please don;t spend to much time on this although I appreciate all the efforts!
Elodie

13. Re: Layered YtD Calculation
Simon Runc Aug 22, 2015 9:19 AM (in response to Elodie Gbedan)...Yes that's actually the way I tend to work too. When I'm in exploration/PoC (Proof of Concept) mode, I will make all kinds of 'horrible' calculations in Tableau (using Previous/Lookups...etc) as I explore the data the kinds of calculations/measures that will work...etc. Once we are all agreed and happy the Viz/Model is good, we then go back to the database and get some of the calculations done there (and also look at how we might reshape the data, aggregate it up...etc. to make for the smoothest/fastest user experience...Don't want to get our Database lady spending hours putting the calcs in, only for me to come back the following week with 'I've changed my mind'!!!)
Yes I see, interesting calculation...a sort of CAGR type calculation looking at 'typical' growth/value over a period of time, and then using that as your proxy for projections....Nice!...btw as you do have 9 years of data it might be worth looking at CAGR  it's much simpler
Definition and Calc : http://www.investopedia.com/terms/c/cagr.asp
An example of the Calc in Tableau: Advanced CAGR Calculation
I have, though, had a chance to look at this, and have come up with (what I think) is what you need. As I've only got 1 year (and 3 weeks) in the data set it's hard to know if I've got it average running correctly over multiple years. Hopefully now we have a 'candidate' formula, we can tweak the compute using to get it to return what you want. If this isn't quite right yet, it might help if you can calculate a couple (of Brands/Items) in Excel so I know when I have the right answer!
So firstly (and mainly due to my laziness!) rather than create a set of views for Brand and then for Item, as well as a set of formulas for POS $ and Units....I've parameterised both, so in one view, and with one set of formulas I can switch between Units/POS $ or Items/Brands. Let me know if you don't see how I've done this, and I can elaborate.
You now have 2 parameters in the worksheet so can switch between the measures and levels. If you do need to have them together (as in you need to show them together) you can simply duplicate all the formulas, and switch out my 'Selected Measure' for POS $ in one set and then POS Units in the other. You can then duplicate the Sheet, and switch out my 'Selected Level' for Item (in one) and Brand (in the other). I've also put all the calculated fields into a folder called 'SR Calculations' to make navigation easier.
So after setting up all the YtD, Multiple...etc using my new 'Selected Measure' field I have the calculated field for FY Projection as
IIF(MIN(INT([Week]))=MIN([Max Week]),SUM([Pos $])*WINDOW_AVG([Mult  Selected Measure  Part Dynamic])+SUM([YTD]), NULL)
I have to use things like MIN([Week]) as I need to use as an aggregate (as we have Week in the level of detail, the Week and MIN of Week is the same...it's just a way round this...I could have also used MAX, AVG, ATTR)
Currently I have set the compute by to Week, as we have for the other calculations, but might need to run the WINDOW_AVG by week/year.
As I said, let me know if this works, and if not, if you can calculate me a couple from this dataset in excel I can have a further play with the 'compute using' to get the desired result.
As we currently can't attach workbooks in the community (there has been a few spam attacks, so the security filters are turned right up), I've posted the workbook to public, where you can download it

14. Re: Layered YtD Calculation
Elodie Gbedan Aug 24, 2015 2:42 PM (in response to Simon Runc)"Don't want to get our Database lady spending hours putting the calcs in, only for me to come back the following week with 'I've changed my mind'!!!)
This is exactly what I want to avoid. You totally understood my world.
Thanks again Simon for looking into this.
I will test the formulas and let you know how it worked out, but from what I read, we are in a good direction
Thanks,
Elodie