8 Replies Latest reply on Sep 20, 2018 3:31 PM by Zachary Bremmer

    LOD Calculation Values

    Zachary Bremmer

      I need some ninja help here. I have a workbook with a single data source that includes sales by customer, group, and country. I'm trying to create a view that compares YTD vs PYTD growth (using a table calc) for three different levels of sales -- sales for an individual customer, sales for that customer's group, and sales for that customer's country. The problem is that some of the customers only have sales this year. When I try to compare YOY YTD sales for members of the same group or country, data does not show up for PYTD for customers that don't have sales last year:



      This prevents me from calculating the growth % between YTD vs PYTD and misleads users (they think that no data = no sales for the previous year). I've tried padding with dates, using ZN() and LOOKUP(ZN()) and I can't figure out how to get PYTD numbers to show up for all customers at the group / country level. Thoughts?

        • 1. Re: LOD Calculation Values
          Joe Oppelt

          I forced a zero on Country Sales for Customer B.

          Is this what you are looking to do?

          • 2. Re: LOD Calculation Values
            Zachary Bremmer

            Not quite. I want to have Country Sales and Group Sales filled in with the correct values for customer B under "Previous." Without those values, my growth numbers are wrong when I do a table calc to figure out growth:



            I want the current growth rate for Country to be 15.1% and for group -71.35% so that it's consistent across all customers.



            I also realize I updated the wrong version of the twbx. Correct version is now posted.

            • 3. Re: LOD Calculation Values
              Joe Oppelt

              OK, I don't get it.

              What should be the correct values for B for Previous?  (I'm trusting that the [Current vs Previous] values are set correctly.)

              • 4. Re: LOD Calculation Values
                Zachary Bremmer

                In order to calculate sales growth correctly, this is what I would need:


                • 5. Re: LOD Calculation Values
                  Joe Oppelt

                  I see what you're doing now.


                  So the same principle has to apply for SUM(Sales).  that's the root of the problem.  There is no mark there at all.  No data.  And unless you blend your data source against a copy of itself and use that structure to force a zero, you'll never see a zero there.

                  (And if you do that blend, the zero you generate will be an aggeregate value, and you won't be able to use it in a FIXED calc anyway.)

                  You could ETL your data to force rows with zero values for Sales for Customer B.


                  But you can force the zero like I did with [Sales with zero].  Now it's a table calc, and once you have a table calc in play, everything that uses it will have to be a table calc.


                  So I made Group Sales table and Country Sales Table.  All three of my calcs are added to the Measure Values shelf.  Notice the settings for the Group and Country table calcs.  One does RESTART EVERY Group, and the other restarts every Country.


                  See attached.

                  • 6. Re: LOD Calculation Values
                    Zachary Bremmer

                    Thanks Joe. That makes sense to me now. How would I go about turning these into % Change calculations since they are already table calcs?

                    • 7. Re: LOD Calculation Values
                      Joe Oppelt

                      I divided previous by current here.  Using LOOKUP is one way to do it.  We could also make a CURRENT COUNTRY SALES calc and a PREVIOUS COUNTRY SALES calc, and divide those.


                      Note the table calc settings for Calculation1.  Different from all the rest.  Now I'm running it along the Current-previous dimension, restarting every country.


                      Table calc settings can get complicated.  But having the visual numbers and making sure the results make sense helps get them set correctly.

                      also, notice this in the table calc setting for Calculation1:



                      Calc1 uses other table calcs.  They are considered "nested".  Often when you add a new table calc that has nested table calcs, if those nested calcs are already on the sheet the latest calc picks up the settings for those nested ones from the way they are already set on the sheet.  But you need to make sure those nested calcs are set the way you want them.


                      So Calc 1 has one particular setting.  Country Sales Table has another, and Sales with zero has a 3rd.  They don't all have to be set the same way!  (And often are not.)

                      • 8. Re: LOD Calculation Values
                        Zachary Bremmer

                        You rock! That gives me what I need. It was the nested calculations that I couldn't wrap my head around.