How to Link the Graph with Year,Month & Quater

[Anonymous]

Hi All,

I've date related graphs which is loaded from MySQL DB and I created Year, Month & Quarter using

LOAD DISTINCT
        Year(Date) as Year,
        Month(Date) as Month,
        Date(MonthStart(Date), 'MMMYY') as MonthYear,
        if(monthstart(Date) <= $(vTodaysDate), 1, 0) as _History,
        'Q' & Ceil(Month(Date)/3) as Quarter,
        Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), Year(Date) & Ceil(Month(Date)/3)) as QtrYear,
        Week(Date) as Week,
        Weekstart(Date) as Weekstart,
        Weekend(Date) as Weekend
        ;
Load
        Date($(vEndDate) - RecNo() +1) as Date
AutoGenerate($(NumberOfDays));

I'm trying to filter using these Year , Month & Quarter but filter not apply with my graphs.How can I link my graph and these Year , Month & Quarter ?

[simenkg · Accepted solution]

LOAD DISTINCT

         Date as %DateKey,

         Date,

        Year(Date) as Year,

        Month(Date) as Month,

        Date(MonthStart(Date), 'MMMYY') as MonthYear,

        if(monthstart(Date) <= $(vTodaysDate), 1, 0) as _History,

        'Q' & Ceil(Month(Date)/3) as Quarter,

        Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), Year(Date) & Ceil(Month(Date)/3)) as QtrYear,

        Week(Date) as Week,

        Weekstart(Date) as Weekstart,

        Weekend(Date) as Weekend

        ;

Load

        Date($(vEndDate) - RecNo() +1) as Date

AutoGenerate($(NumberOfDays));

Then in your fact table you rename the date-field to %DateKey and make sure the formats are the same.

[Anonymous]

Hi simen,

Thanks for the reply.It worked.

[Anonymous]

Hi ,

In my Datasource I have year from 2011 to 2015.How can I display that ?.For Ex:39814 means 01/01/2009 how can I change that ? I want from 01/01/2011 to today.

[simenkg]

You do this with a WHERE statement in your SQL extraction.

SQL Select * from Table Where Date>='01012011';

[Anonymous]

we are unable to understand the code (39814 means 01/01/2009).It will be great if u give some insight in this.

[simenkg]

"The serial numbers used for dates are the same as in Excel: the number of days passed since the 30th December 1899 using the Gregorian calendar. The integer part of the serial number is the date and the fractional part is the time of the day. For example, if the 31st of January 2012 at 6 o’clock in the morning is loaded with US date format, then the number 40939.25 is stored together with the string ‘1/31/2012 6:00 am’"

http://community.qlik.com/servlet/JiveServlet/previewBody/3102-102-4-4191/DateFields-TechBrief-US_v2...

[awhitfield]

Hi Prakash,

it's the number of days passed since the 30th December 1899 using the Gregorian calendar.

Andy