YTD & PYTD as Selection?

[don_qlikview]

Hello Everyone,

I am trying to create YTD and PYTD. I want to show it in a drop down button so that user can come into the application and
select either CYTD or PYTD. I have only month and year field but no day field, how can i do this?

Thanks!!!

[MK9885]

Maybe you can have a Master Calendar which have all those fields you seeking?

[don_qlikview]

Can you give an example? Because what I am looking for is, when user selects CYTD from list box everything should be narrowed down to CYTD and same for PYTD.

Thanks!!!

[MK9885]

It depends on what year you are taking into consideration

Like Min year and Max year (today)

Here is a sample Master Calendar Script I've

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3)  as Quarter

    AUTOGENERATE (12);

       varMinDate = num(date(mid('Your Dates here',1,10 ),'YYYY-MM-DD'));

//       varMaxDate = num(date(mid('2015-12-31',1,10 ),'YYYY-MM-DD'));

       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar:

    LOAD

                   $(varMinDate) + Iterno()-1 as Num,

                   Date($(varMinDate) + IterNo() - 1) as TempDate

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD*,

  AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

  AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load

  trim(date(TempDate,'YYYY-MM-DD')) as DateID,

  date(TempDate,'YYYY-MM-DD') as [Effective Date],

    day(TempDate) as Day,

    TempDate as [US Calendar Format],

    date(TempDate,'YYYY-MM-DD') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1   as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

   inyeartodate(TempDate,today(),0) * -1   as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

    inquarter(TempDate,today(),0) * -1 as [CQ],  // Current Quarter

    inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ],

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,                

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

  date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

  date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

    weekDay(TempDate) as [Week Day],

    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table  QuarterNAME;

You can directly load the above script and you'll get those fields but you need to adjust the dates as per you've in your data model

If your dates are starting from 2010-11-01 then in varMinDate = num(date(mid('2010-11-01',1,10 ),'YYYY-MM-DD'));

Also check the date formats in the script/data What I've given you is YYYY-MM-DD format, you might have different dates.

There might be another solution to this but I personally know by creating Master Calendar. And of course you need to link this Master Calendar to your Fact Table (with DateID) or any date field you've. Usually it is DateID as the key between those 2 tables.

[MK9885]

Check this link below, it might be another way to do it as well.

YTQ, QTD, MTD and WTD

[johnw]

Maybe something like this?

Calendar:
LOAD *
if(Year=year(today()),'YTD'
,if(Year=year(today())-1
and Month<=month(today()),'PYTD')) as Range
;
LOAD DISTINCT
Year
,Month
RESIDENT something
;

This isn't a typical master calendar as there are no dates in it, but it seems like it would meet the need without adding fields you've not asked for. Also depends on what you mean by "year to date" where there's no date. Above, I've interpreted it as "year to month, inclusive".