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
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,
