topic YTD timeframe in QlikView

YTD timeframe

Thu, 18 Nov 2010 16:36:41 GMT

Hi all

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

can any one help?

thanks

YTD timeframe

johnw — Fri, 19 Nov 2010 01:10:43 GMT

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)?

YTD timeframe

Fri, 19 Nov 2010 09:01:10 GMT

Hi John

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

YTD timeframe

Fri, 19 Nov 2010 09:40:26 GMT

I'm also looking for a solution for this problem.

Hope anyone can help.

Thanks

YTD timeframe

johnw — Fri, 19 Nov 2010 19:05:27 GMT

OK, hopefully I understand. If so, this is easily addressed with an AsOf table like this, with example data for 2010 P3:

AsOf:
AsOfPeriod, TimeFrame, Period
2010 P3, Current Period, 2010 P3
2010 P3, Previous Period, 2010 P2
2010 P3, YTD, 2010 P3
2010 P3, YTD, 2010 P2
2010 P3, YTD, 2010 P1
2010 P3, Previous Year, 2009 P12
2010 P3, Previous Year, 2009 P11
...
2010 P3, Previous Year, 2009 P2
2010 P3, Previous Year, 2009 P1

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.

YTD timeframe

Wed, 24 Nov 2010 10:59:27 GMT

Hello,

thanks for your nice description.

But - could you provide a small example. I don't really know how to implement this only with your description.

Thanks in advance for your great support!

Regards,
Andreas

YTD timeframe

johnw — Wed, 24 Nov 2010 19:21:17 GMT

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;

YTD timeframe

Fri, 26 Nov 2010 08:50:11 GMT

Hi John,

you are my Heroe 🙂
After a while of trying this, it's working for my case, too!

Thank you very much!