Values not showing correctly

[deepakqlikview_123]

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

[#2]

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;

[deepakqlikview_123]

Hi Thoms,

Can you please tell me how there are 2 calendar table will works.

Thanks

[#4]

Sorry was an mistake I have correct my last post was the Rest of my first correction - forgot to delete

[deepakqlikview_123]

Hi Thomas,

Can you please suggest on this.

Thanks,

[ramasaisaksoft]

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.