10 Replies Latest reply on Jun 9, 2018 9:35 AM by Jim Dehner

# Lining up Seasonal Variability

Hello,

I work in the produce business and commodity start dates shift around from year to year.  So a start date on cherries may be May 28 last year, and June 8 this year.  My goal is to line up to seasons with a DAY 1, DAY 2, etc. calculation, where DAY 1 will be the first day of shipping for each corresponding year.  Currently this is how the data looks that shows the variability for the years, the days are based on calendar years.

Again my goal is to line the data up so it looks like this (did this manually in excel):

The package workbook is attached, any help would be greatly appreciated!!

Thanks,

Garrett

• ###### 1. Re: Lining up Seasonal Variability

Hi

see the attached

is this what you wanted

if so here are the formulae

all the table calcs are set like this

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.

• ###### 2. Re: Lining up Seasonal Variability

Hi Jim,

Thanks for taking a stab at it.  I think the formulae are all functioning properly, but there are multiple months involved, so the DAY(Ship Date) on the Rows shelf is aggregating to the day level for all months, typically a cherry season is around 70-80 days, so I'm trying to show day 1 - day 80 or whatever the last day # of the longest season is.  But it does look like the 1st date of each season is showing up under day 1, except for 2018 for some reason that is showing up under day 2, something to do with how the table calc functions?

Thanks,

Garrett

• ###### 3. Re: Lining up Seasonal Variability

Ok - lets try this one -

it will go out past the month

here are the new formula - start date by year

running using the start date by year

the running date and index are set like this

note NDY of ship date is the lowest level but it is not on rows or columns - but it is in the detail frame

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.

• ###### 4. Re: Lining up Seasonal Variability

Jim

Yes this is perfect, exactly what I was looking for, thank you!

Garrett

• ###### 5. Re: Lining up Seasonal Variability

Thanks - sorry I didn't get the first time -

Jim

• ###### 6. Re: Lining up Seasonal Variability

Hi Jim,

I've implemented solution into my workbook and its working perfectly.  Now I've thought of another level of functionality I'd like to add as a LOD calc to the top of the viz.  I'd like to show the season to date average price, so say it is now day 10, I'd like to see the average price for the first 10 days of each season and need it to be dynamic based on the current day of the season.  I've got the calc to work for the entire year easily enough but I'm not great with the date calculations.  This has been a good learning experience looking at how you are making this work.

Thanks,

Garrett

• ###### 7. Re: Lining up Seasonal Variability

Hi First a question - your are saying that if the day is day 10 you want the sum of days 1-10

-  are you saying you want to determine the day based on Today() the date function and use the number of days for each year in the viz -

the first 10 days of  2014, 2015, 2016,2017, 2018 ?

Jim

• ###### 8. Re: Lining up Seasonal Variability

Yes that's correct.

So I would have a season to date average for each year for the first 10 days.  Then on day 11 it would be the first eleven days, changing dynamically as we progress through the season based on the current day.

Thanks,

Garrett

• ###### 9. Re: Lining up Seasonal Variability

OK - going to take a little thinking - can't just drop an LOD  in here - we are using table calculations to get the date alignment

• ###### 10. Re: Lining up Seasonal Variability

see the attached

this is not exactly what you envisioned but it will provide the information

As I posted before - the viz uses index() to determine the running date and the number of days into the season -

Index() is a table calculation that is calculated art the bottom of the order of operations - LODs are all calculated well above the table calculations

the attached returns this

-

The viz will update tomorrow to add another day to the table - I haven't figured a way to get just a single record by year for the season to day

Only the column repeating the value you see

here are the formulae

you have seen the first 2 before

set like this

This determines the number of days the today is in the season - need it to determine how many days to sum in each year

note dates are different in each year so you need a fixed scalar number od days

This gets the daily sum of price (not sure that is what you really want - may be sales?)

set like this

This totals the sales for the season to date by year

the total is a nested table calc - the deepest level is running date set as shown - but the outer level is set differently see below

this limits the chart to the correct number of days - it increments each day

You need this filter to force the running date to be calculated out of order

create the viz

Index is set as shown

hope you will be able to use the solution

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.