Calender

[upaliwije]

Vehicle:

LOAD FLAG,

    BCOD,

     date(FROM_DATE,'DD-MM-YYYY h:mm:ss')as  FROM_DATE,

       NAME,

     Amount

     PREMIUM,

     Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

I have loaded above QVD file and created a Pivot Table and the above script is linked to the following master calender

Calender:

LOAD

    Day(FROM_DATE) AS DAY,

    Date(FROM_DATE) AS FROM_DATE,

    Week(FROM_DATE) AS WEEK,

    Year(FROM_DATE) AS YEAR,

    Month(FROM_DATE) As MONTH,

    Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week;

  Load Date(MinDate + IterNo() -1 ) AS FROM_DATE While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(FROM_DATE) AS MinDate,

    Max(FROM_DATE) AS MaxDate  

RESIDENT Vehicle;

In my pivot table when I select all  records I get the correct  Amounts. But If Select a Year , the figure shown in the pivot table is very much less than the actual figures corresponding to the particular year

Eg  Actual No of vehicle for Year 2012 is 10000

But If  Select the year from my Year_List Box  the number become 125

How can This happen? Is it due to a error in my script ? Pls help me to solve this problem

[ashfaq_haseeb]

Hi,

Look at the below post

http://community.qlik.com/docs/DOC-3858

Regards

ASHFAQ

[sujeetsingh]

Load

    Min(FROM_DATE) AS MinDate,

    Max(FROM_DATE) AS MaxDate

RESIDENT Vehicle;

CalendarMaster:

LOAD

    Date(FROM_DATE) AS FROM_DATE,

    Year(FROM_DATE) AS Year,

    'Q' & Ceil(Month(FROM_DATE) / 3) AS Quarter,   

    Month(FROM_DATE) As Month,

    Day(FROM_DATE) As Day,

    Week(FROM_DATE) As Week;

Load Date(MinDate + IterNo() -1 ) AS FROM_DATEWhile (MinDate + IterNo() - 1) <= Num(MaxDate);

[jagan · Accepted solution]

Hi,

The key field FROM_DATE is having in different formats one with timestamp and another without timestamp.

Vehicle:

LOAD FLAG,

    BCOD,

     date(Floor(FROM_DATE),'DD-MM-YYYY')as  FROM_DATE,

       NAME,

     Amount

     PREMIUM,

     Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

Calender:

LOAD

    Day(FROM_DATE) AS DAY,

    Date(FROM_DATE, 'DD-MM-YYYY') AS FROM_DATE,

    Week(FROM_DATE) AS WEEK,

    Year(FROM_DATE) AS YEAR,

    Month(FROM_DATE) As MONTH,

    Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week;

  Load Date(MinDate + IterNo() -1 ) AS FROM_DATE While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Floor(Min(FROM_DATE)) AS MinDate,

    Floor(Max(FROM_DATE)) AS MaxDate 

RESIDENT Vehicle;

Hope this helps you.

Regards,

jagan.

[Anonymous]

Hi,

First of all, is it really necessary to create Calendar table for above scenario. If it is required can we just create based on FROM_DATE. For example below,

Calender:

LOAD

   FROM_DATE,

    Day(FROM_DATE) AS DAY,

    Date(FROM_DATE) AS FROM_DATE,

    Week(FROM_DATE) AS WEEK,

    Year(FROM_DATE) AS YEAR,

    Month(FROM_DATE) As MONTH,

    Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week

Resident Vehicle;

If it is not require, you can create all these calendar fields inside Vehicle table. For example below,

Vehicle:

LOAD FLAG,

    BCOD,

     Date(FROM_DATE,'DD-MM-YYYY h:mm:ss')as  FROM_DATE,

     Day(FROM_DATE) AS DAY,

    Date(FROM_DATE) AS FROM_DATE,

    Week(FROM_DATE) AS WEEK,

    Year(FROM_DATE) AS YEAR,

    Month(FROM_DATE) As MONTH,

    Month(FROM_DATE) &'-'& week(FROM_DATE) As Month_Week,

      NAME,

     Amount

     PREMIUM,

     Vehicle_No

FROM

[..\QVD\FACT_Table\Vehicle_Type.qvd]

(qvd);

Thanks

[upaliwije]

Thanks