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?
Tableau Data Oct 2018.twbx 80.9 KB
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
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
Tableau Data Oct 2018.twbx 56.4 MB
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
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:
Hope it helps! Thx, Don
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
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