9 Replies Latest reply on Mar 22, 2016 3:51 AM by Carl Slifer

    Pulling out Last Data based on Dimension (:()

    Sunil Rai

      Dear all,

       

      Is there a way for me to pull out last week inventory data without using the

       

      IF [Week End Date]== { FIXED:MAX([Week End Date])} THEN [Inventory Qty] END function. Because this function takes the latest from the entire dataset. I don't have any dates. My dates at FY, Fiscal Month, fiscal Week. and these are all strings. In the chart below, there is both 2015 and 2016 data but I hid 2015 because I want to see only 2016. This allows me to still see the YoY growth. However, I have one issue. For END INVENTORY, it only pulls out 2016 data (because my entire data is still march 2016. In the filter I have fiscal week 23 to 40. What I need is to pull the ending week (fiscal week 40 inventory) for both 2015 and 2016. The max date function only does so for 2016.  Any idea how to make it such that it pulls out the latest fiscal week inventory only.

       

      When I use replace weekenddate (which is a field I cant use anymore) with fiscal week in the formula above, I get nothing. Any help will be greatly appreciated. Thanks!

       

       

       

       

        • 1. Re: Pulling out Last Data based on Dimension (:()
          Sunil Rai

          Bascially, how can I do a calculation to return end inventory based on the fiscal week that I filter for. Since fiscal week is 33, and my dashboard has 2 years, it should return 33 and 33 for both years.

          • 2. Re: Pulling out Last Data based on Dimension (:()
            Carl Slifer

            Howdy Suni,

             

            Can you provide a sample of what your underlying data looks like? I'll give some general examples and hope it works but without seeing the structure or a sample workbook it may be a bit difficult. Lets first chat about what happened when we used our LOD and explain why they are in fact amazing.

            {FIXED [Dimension]: SUM([Measure])}

            First you define what type of LOD you will be using. I say it out loud. Right now I want to FIX my detail at blank dimension and return the SUM of [Measure] for each row.

            {FIXED [Dimension]: SUM([Measure])}

            Choose at what level of detail you want to group and sum. For instance if you put fiscal week here from your example you would then return the SUM of [Measure] for each Fiscal week but you would return it per every row

            {FIXED [Dimension]: SUM([Measure])}

            And here you tell Tableau what value you wish to aggregate and return for each row.

             

            In your case we never defined that second part and because of this we would pull from the entire database and not partition it into sections based on the dimension. Now, in order to help if you provide a sample of the data structure we may actually be able to use same type of LOD trick. But we would be matching if the year was the max and if the week was the max instead of trying to do it in one fell swoop. Something like...

            {FIXED : MAX(FiscalYear)} = [FiscalYear]  

            // The above will check the entire database and return true or false for all records. It will return true if the year is the maximum in the database

            AND

            {MAX(IF {FIXED :(MAX([FiscalYear]} = [FiscalYear] THEN [FiscalWeek] END)} = [FiscalWeek]

            //This above part will look at all the rows in your database and only for those that are the same as the last year it will return the fiscalweek field. It then finds the maximum week of those in the last year

             

            Both parts of above need to be together in one Boolean Function. Just copy and paste the 5 lines above as needed and it will return the last weeks of data. If you need to do this per division you need o add [Division] in after each mention of FIXED and before the ';'

             

            Cheers!

            Carl Slifer

            InterWorks

            • 3. Re: Pulling out Last Data based on Dimension (:()
              Sunil Rai

              Hey Carl,

               

              Thanks a lot. Can I ask if I put a fiscal week filter in, will the return by for the final week in the filter?

               

              Thanks for your help.

               

              Cheers,

              Sunil

              • 4. Re: Pulling out Last Data based on Dimension (:()
                Sunil Rai

                Sorry Carl,

                 

                I am quite new to this and my data is really quite confidential but it is sales data by week.

                 

                It has Fiscal Year/Fiscal Month/Fiscal Week. as a string field.

                 

                2014   Jan   Week 1

                                    Week 2

                                    Week 3

                                    Week 4

                 

                So what I need is to pull out Week 4 for 2014 and Week 4 for 2015. (If my fiscal week filter starts from lets say week 1 to week 4) I want the latest inventory pulled based on the filter. This will allow the data to refresh fast automatically.

                 

                 

                Actually I kind of don't get it haha, sorry I am really quite amateur at LOD. I wish I could grasp it baaah.

                • 5. Re: Pulling out Last Data based on Dimension (:()
                  Carl Slifer

                  Hi Sunil,

                   

                  Again, I can't see your structure some people report fiscal week like 2015W37 or 37.2015 or even just 37. It's harder to just say for certain without seeing it. You said you had fiscal year, fiscal month, and fiscal year before so I assume you are just reporting 37. Even in this case it won't be the MAX for your data set. Let's assume we have two years of data and currently we are only in week 30. Well last year we had as high as week 52. So the MAX for the entire database is week 52 and your week 30 from the current year will get filtered off. With more details we can help more but right now I'm shooting from my hip in the dark so you have to forgive me if I'm off. It's also still a bit confusing as you have 8 measures and remember you're the expert at your data and your business and I have no clue the business requirements or logic.

                   

                  What I can suggest is as silly as it sounds asking yourself questions out loud. What is my filter doing? It's taking the max. The max of what? Just this year or all the years? Did I tell it to look at just this year? Then no, its just pulling all the years then. Some folks think I'm crazy if I talk out loud to myself like this, and I might be crazy for other reasons, but this situation I do it to help me work through the logic.

                   

                  Cheers!

                  Carl Slifer

                  InterWorks

                  • 6. Re: Pulling out Last Data based on Dimension (:()
                    Sunil Rai

                    Hey Carl,

                     

                    Thanks anyways! Really appreciates, Ill try think it over

                     

                    Cheers,

                    Sunil

                    • 7. Re: Pulling out Last Data based on Dimension (:()
                      Carl Slifer

                      Hi Sunil,

                       

                      You won't be an amateur by the time you read this blog and finish these 15 expressions on your own (they do take a bit).

                      http://interworks.co.uk/blog/tableau-level-detail-calculated-fields-v9/

                      Top 15 LOD Expressions | Tableau Software

                       

                      I would start with the blog of course, he does a fine job in describing what is occurring.

                       

                      If you need both the most recent year and the previous year then you will still maximum week for the maximum year which in this case says Week 4. We will then take that and return all week 4 in our database by using a filter like....

                      {MAX(IF {FIXED :(MAX([FiscalYear]} = [FiscalYear] THEN [FiscalWeek] END)} = [FiscalWeek]

                      This checks for the most recent year and the most recent week, Whatever that most recent week value is it will apply it to the entire database so you will get values from previous years (but same week) as well.

                       

                      From here you can do things like find the year over year change, define growth or shrinkage between the two. I'm not going to be able to help much passed this but what I will give you a hint is you can use a table calc for sales to see the table calc between years but then hide the previous year (2014) so that its not in the view. This way you've used the values from 2014 but only get the % change or difference in the most recent year.

                       

                      Cheers!

                      Carl Slifer

                      InterWorks

                      • 8. Re: Pulling out Last Data based on Dimension (:()
                        Sunil Rai

                        Hey Carl,

                         

                        Thanks for this. Really really appreciates it.

                         

                        Can I ask you one other question please.

                         

                        I am using the formula IF [Week End Date]== { FIXED:MAX([Week End Date])} THEN [Inventory Qty] END to get the latest inventory in the dataset.

                         

                        Is there a way to tweak it such that if the max date is null or 0 then it will look at the previous weekenddate and pull the inventory number, and so on.

                         

                        Cheers,

                        Sunil

                        • 9. Re: Pulling out Last Data based on Dimension (:()
                          Carl Slifer

                          Hi Sunil,

                           

                          The function MAX ignores nulls and 0 would not be the max. I think the question is if the inventory value is 0 or null then go back a week? At this point you start needing to use something besides LODs or you really need to plan it out carefully. You can make a field that tells you if a field is null or 0 or not.

                           

                          ZN([Inventory]) !=0

                          And call it Inventory Check. when it is true then the value is not 0 or null, when false it is.

                           

                          IF [Week End Date]== { FIXED: MAX(IF [Inventory Check] THEN [Week End Date]  END)} THEN [Inventory Qty] END

                           

                          So what the above does is IF the field has an inventory value besides 0 or null it returns the date and then it returns the maximum of all of those dates and compares it to the week end date field itself. 

                           

                          Hopefully that puts you on the right path. You could also filter out any weeks with an inventory of 0 or null. This should work as well, but because we are using a FIXED LOD we would want to make this filter a context filter so that it occurs before the FIXED expression runs.

                           

                          Cheers