Forum Discussion

whothefoo's avatar
whothefoo
Frequent Visitor
6 years ago
Solved

Single year from date range

Hi, my suspicions are that this may be impossible but...

I am kind of reinventing the wheel in an attempt to increase my BI knowledge, so I am making a football based BI as it's something I have a strong interest in... 

 

Problem is football runs from seasons, but the data I can get from available sources often works from season ranges, for example a player could be at a club from 2000-2007, but I want to create the squads based on this sort of info so I can ask 'who was in the 2000-2001 team?' 

 

So the question: Is there a way of separating out the year groups into duplicate rows?

 

Edgar Chadwick
England		FW1888–1898

 

becomes 

Edgar Chadwick  FW 1888-1889

Edgar Chadwick  FW 1889-1890

Edgar chadiwck  FW 1890-1891

and so on...

 

Reason being I am going to get manager info and build the squads so its possible to choose a season - 1889-1890 for example and it will show Edgar Chadwick in that squad.

 

Hope that makes sense and any help/alternatives would be greately appreciated.

 

Many thanks

 

Wayne

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.

    • whothefoo's avatar
      whothefoo
      Frequent Visitor
      6 years ago

      Yes :) took a bit of playing around with things I didn't understand but it's sorted now.

      Thank you

       

      If anyone is interested it's a mixture of using left and right commands to make two columns with the start and end year from the 2010-2015 style range.

       

      Subtract the start column from the end to give a count of how many duplicate rows you need then follow the below:

      https://www.youtube.com/watch?v=cXb308NPU2M

       

      * I did have to add the final index column into the formula when expanding the rows but it's given me a duplicate row for each with a value of 0-however many dupliates there are.

       

      Now I can just add that value to the start date and add one to create the end date and concat back to get a start to end range - for 2010-2015 - index 3 - add index (in the case 3) to start (2010) add one more for the end date and concat = 2013-2014.

      For the next dipicate row the index is 4 so it will make 2014-2015.

       

      phew lol