Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please find attached Qvw file.
For showing month,quarter,year,Day properly I have created new calendar table using resident data(On retail table)
I have left joined calendar with organizationmaster,but it seems results are not displaying correctly.
Please suggest.
Thanks
First you are matching the Calendar by DealerID as KeyField - to create an Calendar you should use the DATEFIELD as KeyField,
Second I think you having dates in your source data which aren't format correct try to use date#() instead of date() to cast a right formated date by Loading it.
Somthing like this:
Dealer1:
LOAD ......
date#(DealerInvoiceDate,'DD-MM-YYYY') as DealerInvoiceDateKey
.......;
Tmp_MinMaxDate:
Replace Load
Min(%DealerInvoiceDateKey) As MinDate,
Max(%DealerInvoiceDateKey) As MaxDate
Resident Dealer1;
/******************** Set Vars *******************/
Let vMinDate = Peek('MinDate', 0, 'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDate');
Drop Table Tmp_MinMaxDate;
Datefield:
Replace Load date($(vMinDate)+Iterno()-1) As Datefield
Autogenerate 1
While $(vMinDate)+Iterno()-1 <= $(vMaxDate);
Calendar:
Replace Load *,
Datefield As %DealerInvoiceDateKey,
Datefield As MyDate,
Year(Datefield) As Year,
Year2date(Datefield)*-1 As YTDcurYear,
Year2date(Datefield,-1)*-1 As YTDlastYear,
Month(Datefield) As Monat,
date(monthstart(Datefield), 'MMM-YYYY') As curMonth,
'Q' & Ceil(Month(Datefield) / 3) As Quarter,
week(Datefield) As Week,
week(Datefield) & '-' & Year(Datefield) As curWeek,
Day(Datefield) As Day,
weekDay(Datefield) As WeekDay
Resident Datefield
Order by Datefield Asc;
Drop Table Datefield;
Hi Thoms,
Can you please tell me how there are 2 calendar table will works.
Thanks
Sorry was an mistake I have correct my last post was the Rest of my first correction - forgot to delete
Hi Thomas,
Can you please suggest on this.
Thanks,
Hi Deepak ,
can u please check/Modify u r code like below for Date purpose.
//Shows Data for Yearwise
Year("Quote Date") as Year_QU,
//Shows Data for Month
Month("Quote Date") as Month_QU,//
//Shows Data for Day
Day("Quote Date") as Day_QU,
//Shows Data for Quarter
If(Num(Month([Quote Date]))=1,01,If(Num(Month([Quote Date]))=2,02,If(Num(Month([Quote Date]))=3,03,
If(Num(Month([Quote Date]))=4,04,If(Num(Month([Quote Date]))=5,05,If(Num(Month([Quote Date]))=6,06,
If(Num(Month([Quote Date]))=7,07,If(Num(Month([Quote Date]))=8,08,If(Num(Month([Quote Date]))=9,09,
If(Num(Month([Quote Date]))=10,10,If(Num(Month([Quote Date]))=11,11,If(Num(Month([Quote Date]))=12,12)))))))))))) as Month_Nbr_QU,
If(Num(Month([Quote Date]))=1 or Num(Month([Quote Date]))=2 or Num(Month([Quote Date]))=3,'Q1',
If(Num(Month([Quote Date]))=4 or Num(Month([Quote Date]))=5 or Num(Month([Quote Date]))=6 ,'Q2',
If(Num(Month([Quote Date]))=7 or Num(Month([Quote Date]))=8 or Num(Month([Quote Date]))=9 ,'Q3',
If(Num(Month([Quote Date]))=10 or Num(Month([Quote Date]))=11 or Num(Month([Quote Date]))=12 ,'Q4')))) as Quarter_QU,
i hope it will help u.