11 Replies Latest reply on Jul 3, 2018 8:49 AM by Ganesh Vijay Kumar

    Calculating Missing weeks in data!

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to calculate the missing weeks in the data. Below image shows how is my existing data.

       

      Existing Data.PNG

       

      The below image shows what is expected results:I am trying to calculate "Calculated Calendar Week" and "Calculated week Diff"

       

      Expected Results.PNG

       

      Please let me know if this is achievable. Any help in achieving the desired results would be deeply appreciated.

       

      Thanks,

      Ganesh

        • 1. Re: Calculating Missing weeks in data!
          Steve Martin

          Hi Ganesh,

           

          Given that your week numbers are simply integers, you should be able to use a lookup function with previous_number() to generate the missing numbers and to return zero for these missing weeks in two separate calculations

           

          Steve

          • 2. Re: Calculating Missing weeks in data!
            Jim Dehner

            see below

            right click the date pill and select show missing dates

             

             

            then yu can use the table calc to get the differenc

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Calculating Missing weeks in data!
              Steve Martin

              Hi Jim,

               

              Show missing values is only relevant for when a full date is presented, and, will only work with dates, no other values will work regardless of pattern-similarity.

              In this instance, Ganesh's data is only presenting integers representing weeks with no actual dates included in the set, so this method will not work; which is why I chose not to mention it here.

               

              Steve

              1 of 1 people found this helpful
              • 4. Re: Calculating Missing weeks in data!
                Ganesh Vijay Kumar

                Thanks a lot for the response Jim and Steve.

                 

                Steve, Yes you are right the dates or weeks are not available in the original dataset itself for the missing lines. As mentioned in my post, If there is a missing week, I just need to get it and measure value for that missing week could be zero.

                 

                Please let me know if this is possible.

                 

                Thanks,

                Ganesh

                • 5. Re: Calculating Missing weeks in data!
                  Jim Dehner

                  Hi

                  IF there is not record at all in the data set for the missing week you can create a file of dates "New Dates" that  includes every week ( i.e. not missing weeks) in a column

                  Add it into the tableau and JOIN if with your current data set on date - Use the "new dates" in your viz and bring the values in using a ZN() function to 0 out the null values

                   

                  Jim

                  If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                  1 of 1 people found this helpful
                  • 6. Re: Calculating Missing weeks in data!
                    Ganesh Vijay Kumar

                    Hi Jim,

                     

                    This won’t be straight forward join, since I am using Snapshot data. Let me try to explain it again

                     

                    I am working on forecast data, customers are sending us forecast every week for future dates. Since they are sending the forecast every week the data in the ERP system we use gets overwritten. Hence are capturing the snapshots every week.

                     

                    In the below example:

                    if we consider my current week is 201826 and let’s see how we calculate Weekdiff.

                     

                       

                    •   0 = Current week demand (201826 – 201826 = 0)

                       

                    •   1= 1 week before demand for week 201826 is 2400 (201826-201825 = 1)

                       

                    •   2= 2 weeks before demand for week 201826 is 2400 (201826-201824 =2)

                       

                    •   3= 3 weeks before demand for week 201826 is 2400 (201826-201823 =3)

                       

                    •   4=  4 weeks before No data available in ERP (201826-201822 =4 )   : Since there is not data here I am trying to create a calculated field and get 201822 and 4  and measure value could be either 0 or previous weeks value

                       

                    •   5= 5 weeks before  No data available in ERP (201826 – 201821 = 5 : Since there is not data here I am trying to create a calculated field and get 201821 and 5 and measure value could be either 0 or previous weeks value

                       

                    •   6=  6 weeks before demand for week 201826 is 2400 (201826-201820 =6)

                     

                    Calendar Week

                     

                    Snapshot Calendar Week

                     

                    Week Difference

                     

                    Demand

                     

                    201826

                     

                    201820

                     

                    6

                     

                    2400

                     

                     

                     

                    201823

                     

                    3

                     

                    2400

                     

                     

                     

                    201824

                     

                    2

                     

                    2400

                     

                     

                     

                    201825

                     

                    1

                     

                    2400

                     

                     

                     

                    201826

                     

                    0

                     

                    2400

                     

                    201827

                     

                    201821

                     

                    6

                     

                    2400

                     

                     

                     

                    201822

                     

                    5

                     

                    0

                     

                     

                     

                    201823

                     

                    4

                     

                    0

                     

                     

                     

                    201826

                     

                    1

                     

                    2400

                     

                     

                     

                    201827

                     

                    0

                     

                    2400

                     

                     

                    Hope this avoids the confusion.

                     

                    Thanks,

                    Ganesh

                    • 7. Re: Calculating Missing weeks in data!
                      Jim Dehner

                      OK

                      the principle is the same - you need to create a framework to hang the data on (join)

                      the framework needs to include each "date value"  you expect from the customer format is not important - you just need a place in the framework to the "Date Value" indicators that will be populated with a value or null

                       

                      the calculation is a different story - still sounds like a table calc - they just do a lookup and take the current value in the tabel from the previous

                       

                      Jim

                      If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                      • 8. Re: Calculating Missing weeks in data!
                        Ganesh Vijay Kumar

                        Hi Jim,

                         

                        Based on your suggestion I created a new dataset and tried to join. I am using Hana live connection as primary data source. Got to know that cross data join(Full outer Join) for Hana and any other data source do not work.

                         

                        Thanks,

                        Ganesh

                        • 9. Re: Calculating Missing weeks in data!
                          Jim Dehner

                          Didn't know that - will it let you left joint using the new frame work as the left file?

                          1 of 1 people found this helpful
                          • 10. Re: Calculating Missing weeks in data!
                            Ganesh Vijay Kumar

                            Hi Jim,

                             

                            The join worked now and I have the missing data, I had to select “Null” also in the filters I used.

                             

                            But now I encounter another problem when I am trying to write a calculated field.

                             

                            I have two data Sources.

                             

                            One is snapshot data where I get my demand from as shown in the image below. The two lines highlighted in red color is missing in the original dataset. Hence I have created a separate dataset with all the weeks and did a join as suggested by you.

                             

                            Calendar Week

                             

                            Snapshot week

                             

                            Week Diff

                             

                            Demand

                             

                            201826

                             

                            201820

                             

                            6

                             

                            2400

                             

                            201826

                             

                            201821

                             

                            5

                             

                            0

                             

                            201826

                             

                            201822

                             

                            4

                             

                            0

                             

                            201826

                             

                            201823

                             

                            3

                             

                            2400

                             

                            201826

                             

                            201824

                             

                            2

                             

                            2400

                             

                            201826

                             

                            201825

                             

                            1

                             

                            2400

                             

                            201826

                             

                            201826

                             

                            0

                             

                             

                             

                             

                            Another one is booking data:

                             

                            Calendar Week

                             

                            Order Quantity

                             

                            201826

                             

                            2400

                             

                             

                            I have to blend Calendar week in both the tables and get the Order quantity in Calendar week 201826 and calculate the variance for each week demand Versus Order quantity.

                             

                            Below table shows the expected results:

                             

                            Calendar Week

                             

                            Snapshot week

                             

                            Week Diff

                             

                            Demand

                             

                            Booking

                             

                            Variance

                             

                            201826

                             

                            201820

                             

                            6

                             

                            2400

                             

                            2400

                             

                            100%

                             

                            201826

                             

                            201821

                             

                            5

                             

                            0

                             

                            2400

                             

                            0%

                             

                            201826

                             

                            201822

                             

                            4

                             

                            0

                             

                            2400

                             

                            0%

                             

                            201826

                             

                            201823

                             

                            3

                             

                            4800

                             

                            2400

                             

                            200%

                             

                            201826

                             

                            201824

                             

                            2

                             

                            1200

                             

                            2400

                             

                            50%

                             

                            201826

                             

                            201825

                             

                            1

                             

                            2400

                             

                            2400

                             

                            100%

                             

                            201826

                             

                            201826

                             

                            0

                             

                             

                             

                            2400

                             

                             

                             

                             

                            I wrote the mentioned formula after blrnding Calendar week.

                             

                            IF ATTR() = 0 THEN SUM(.[Order Quantity])

                            ELSE SUM(ZN()) END

                             

                            Below are the results under Column named Final Value (Booking)

                             

                             

                             

                             

                            In the above image “line deleted in the system”  are missing weeks and I am getting 0 for that particular line instead of 2400.

                             

                            How do I have it fixed and get 2400 on each line as expected.

                             

                            Your help will be deeply appreciated.

                             

                            Thanks,

                            Ganesh

                            • 11. Re: Calculating Missing weeks in data!
                              Ganesh Vijay Kumar

                              Attached is the sample Twbx file for your reference.

                               

                              Instead of null I want the Order quantity Number