I am trying to create a table that contains my timeframes, for an example I want to create a time frame called YTD. I have my table containing actuals,forecast and YAG data for Periods 1,2and 3. I want to creat a list of dropdowns that will contain the timefames i.e YTD
my YTD should = the sum of periods 1,2 and 3 and when I select YTD I would want all my data on the tab to change and show me the sum of all my periods.
I need to know what the best approach for creating this is and how to do it
What is a period? What other time frames do you want other than YTD? Are these timeframes based ONLY on today's date? Are they based on a selected date? Are they based on whatever date is in the dimension of a chart, so you could see YTD for multiple dates at the same time? Do you want to be able to see more than one time frame at the same time, such as seeing today's data, month to date data, and year to date data in the same chart? Or do you want to force them to see one and only one time frame so that it affects every chart everywhere and not just charts referencing the time frame(s)?
a period is a month, so january would be P1, Feb P2, March P3 and so on . YTD would be the time frame which would show me the data upto the latest period so if I was in March YTD would = P3. I do want to create other timeframes like current period, previous period and , previous year, but i only want to see one timeframe at the time by making the timeframe selectable.I want to force them to see one and only one time frame so that it affects every chart everywhere and not just charts referencing the time frame
Create a list box for TimeFrame, select one, and then set it to "Always One Selected Value". Use AsOfPeriod in all of your charts instead of Period. Now whatever AsOfPeriod or periods they're looking at, and whatever TimeFrame they've selected, the AsOf table will map them to the correct real periods. If they select 2010 P3 and Previous Period, they'll actually be looking at 2010 P2. If they select 2010 P3 and YTD, they'll actually be looking at 2010 P1 - P3.
Let me know if you need help generating the table, or further clarification.
But... maybe this is overkill. It allows you to select periods, or use periods in charts, so that you could, for instance, select the entire year of 2010, and if you selected TimeFrame YTD, each month would show the YTD total through that month. Maybe that's more than what you need? If you ONLY need the time frame based on the current month, then you don't need this table. Well, you still need a table, but it doesn't need the AsOfPeriod, because that will ALWAYS be the current period. So it would just have TimeFrame and Period, and you'd only generate rows based on the current period.
See attached example based on what I posted. Hopefully it's roughly what you're looking for. I'll put the script below for people who are unable to open examples.
Data: LOAD date(addmonths(makedate(2008),recno()-1),'YYYY PM') as Period ,ceil(rand()*10) as Value AUTOGENERATE 36 ;
// A calendar would normally be more complicated than this. // This calendar provides no useful data, but I wanted to // show that you load the periods from the calendar, NOT // from the data. Calendar: NOCONCATENATE LOAD Period RESIDENT Data ;
// And the periods table is similary useless, since both // the Data and Calendar tables have the same periods. // But in a real application, this gives us a short // list of periods instead of having to continue doing // load distinct. Periods: NOCONCATENATE LOAD DISTINCT Period RESIDENT Calendar ;
AsOf: LOAD Period as AsOfPeriod ,Period ,'Current Period' as TimeFrame RESIDENT Periods ; CONCATENATE (AsOf) LOAD Period as AsOfPeriod ,date(addmonths(Period,-1),'YYYY PM') as Period ,'Previous Period' as TimeFrame RESIDENT Periods ; CONCATENATE (AsOf) LOAD Period as AsOfPeriod ,date(addmonths(Period,1-iterno()),'YYYY PM') as Period ,'YTD' as TimeFrame RESIDENT Periods WHILE iterno()<=month(Period) ; CONCATENATE (AsOf) LOAD Period as AsOfPeriod ,date(addmonths(yearstart(Period,-1),iterno()-1),'YYYY PM') as Period ,'Previous Year' as TimeFrame RESIDENT Periods WHILE iterno()<=12 ; INNER JOIN (AsOf) LOAD Period RESIDENT Periods ; DROP TABLE Periods;
