8 Replies Latest reply on Oct 16, 2018 6:05 AM by Jason Bunce

    Aggregating Data With Conditions

    Jason Bunce

      Hi

       

      I really don't know where to start here

       

      I have a set of data differentiated by date and material no. For any given date I need to aggregate the demand for the following 10 days unless the material no changes within 10 days in which case I want to return a null

       

      I don't have every date in a given range so it needs to work on date calculations vs a simple count

       

      Thanks in advance

       

      Jason

        • 1. Re: Aggregating Data With Conditions
          Don Wise

          Hi Jason,

          See if the attached works for you? If you don't need to see each individual date, and just need to see by material what the ensuing 10-day forward amount is, just remove the Calendar Date field from columns.  Also, I didn't understand the Null condition. Could you explain that out a bit more?

           

          Thx, Don

          Screen Shot 2018-10-11 at 1.29.36 PM.png

          • 2. Re: Aggregating Data With Conditions
            Jason Bunce

            Thanks Don

             

            This isn't really what I am after but I probably didn't explain it very well

             

            I am looking for the following returns

             

            If we stick with 03691B for simplicity

             

            For Oct 11th 2018  I would be looking for 630 as the sum of demand for the next 10 days and similarly for Oct 12th 669. This would need to be based on some sort of date function as not every date is included.

             

            To the Null question the last date that has 10 days worth of future demand is the 3rd April which should return 525, the 4th April only has 9 subsequent days and thus should return a Null value

             

            Thanks again for your help

             

            Jason

            • 3. Re: Aggregating Data With Conditions
              Don Wise

              Hi Jason,

               

              I took another crack at it; but i'm still not sure it's going to meet your needs.

               

              I did fix the sum issue in this version so that lines up with what you're looking for Material Qty. For the Null question, when the WINDOW_COUNT function detects that there are less than 9 days in the view, then the view will go blank.  This works for when all materials are selected (and there's no ensuing data for all materials) or if a single material is selected. The calculation that I'm using (WINDOW_COUNT) will require that the Calendar Date remain in the view. However, if there are multiple materials selected and one of them has no ensuing data, then it will simply display blank data for each date, however a total remains? So not sure if that's going to work for you.

               

              Regardless, hope this helps unless someone else on the forums has some ideas!  Thx, Don

              Screen Shot 2018-10-12 at 2.17.56 PM.png

              Screen Shot 2018-10-12 at 2.20.15 PM.png

              • 4. Re: Aggregating Data With Conditions
                Jason Bunce

                Thanks so much Don

                 

                Unfortunately I have an older version of Tableau so can't open the TWBX file

                 

                If you get the time can you ping me the calc for 10 Day Period and I will try to replicate

                 

                It doesn't feel like it is going to work but I do appreciate your efforts

                • 5. Re: Aggregating Data With Conditions
                  Don Wise

                  Hi Jason,

                  No worries...

                   

                  Calc Name: [DATE IS NULL]
                  Calculation: IF WINDOW_COUNT(MAX([Calendar date]))<10 THEN 0 END

                  Drag to Filters Card. Set to compute using Table Across.

                   

                  Calc Name: [10-Day Period]

                  Calculation: [Calendar date]>=[DateSelect] AND [Calendar date]<=DATEADD('day',9,[DateSelect])

                  Drag to Filters Card. Set to True.

                   

                  [Calendar Date] set to Day of Calendar Date and is placed on Columns.

                  [Material] is on Rows.

                  SUM([Demand Quantity]) is on Marks Card.

                   

                  I also created the following Parameter to select individual dates, pasting the data in from the [Calendar Date] data:

                  Screen Shot 2018-10-15 at 9.22.06 AM.png

                   

                  Hope it helps! Thx, Don

                  • 6. Re: Aggregating Data With Conditions
                    Jason Bunce

                    Thanks Don I have learned some new techniques which is great but it doesn't get me where I need and now I understand where you are leading I don't think it is a refinement of this.

                     

                    My actual dataset is approaching 50k lines comprising and I was looking to be able to have either a value or null on each of these lines that I can then do further calculations i.e. another field * 10 days avg demand

                     

                    Sorry if I didn't make it clear up front and sent you on a wild goose chase

                    • 7. Re: Aggregating Data With Conditions
                      Don Wise

                      Hi Jason,

                       

                      No worries...if I think of something I'll circle back around...this thread has gotten over 100 views.

                       

                      We all tend to chime in when someone sees/knows something that might work...so hang in there!  Don

                      • 8. Re: Aggregating Data With Conditions
                        Jason Bunce

                        Cheers

                         

                        On Mon, Oct 15, 2018 at 4:35 PM Don Wise <tableaucommunity@tableau.com>