1 2 Previous Next 16 Replies Latest reply on Aug 27, 2015 3:15 AM by Simon Runc Branched from an earlier discussion.

    Layered YtD Calculation

    Elodie Gbedan

      ...this discussion follows on for further requirements, from the following post

      Create % share calculated field relatively to my Set "Top 3"

       

      I've branched this off to a new thread as this solution doesn't reflect the original title/topic so will make it easier for others to find. however click on the link above to see where the requested calculation comes from.

      Simon

      Ambassador Badge Very Small.jpg

       

       

      Merci!

      Cool! it worked. I am really happy about having the multiple key figure available and handy.

       

      The reason why I set up this measure is to be able to provide a quick FY Sales projections based on the most recent week of sales.

      I am looking for some help on how to write this formula now,

       

      Now that I have the multiple by week, I would like to create a calculated field that basically would be called "2015 FY Projections"

       

      Consider that I receive POS sales for week 31 ( which the most recent week of sales data in 2015)

       

      2015 FY projections=  POS of  week 31 (any week after week 31 should be empty) *average Multiple (2012-2013-2014) week 31+ YTD realized ( week 1 to week 31)

      I want this to be able to be at item level and brand level and even be able to see it by retailer when I drag this in the view

       

      What would you suggest

      Would you need  file demo or the previous one will still be useful?

       

      Thanks,

      Elodie

        • 1. Re: Layered YtD Calculation
          Simon Runc

          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

            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?


            MaxWeek.jpg

            • 3. Re: Layered YtD Calculation
              Simon Runc

              whoops, got my brackets in the wrong place!

               

              Try

               

              INT(RIGHT(STR([MaxWeekYear]),2))

              • 4. Re: Layered YtD Calculation
                Elodie Gbedan

                Worked.

                I will continue and let you know how the process went.

                • 5. Re: Layered YtD Calculation
                  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'YTD.jpg

                  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

                    ...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

                      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 (2012-2013-2014) multiple + YTD

                       

                      Thanks

                      • 8. Re: Layered YtD Calculation
                        Elodie Gbedan

                        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

                          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 2012-2014, 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

                            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 1-31)

                                                   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 (2012-2013-2014)+ 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

                              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 trade-off 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 data-structuring 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/early-next-week 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

                                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

                                  ...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 re-shape 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 data-set 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

                                   

                                  | Tableau Public

                                  • 14. Re: Layered YtD Calculation
                                    Elodie Gbedan

                                    "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

                                    1 2 Previous Next