Year, Quarter, Month Selection List Box

[#1]

Hi All,

I have written this code

--------------------------------------

LOAD DISTINCT

    [Invoice Date],

    Year([Invoice Date]) as Year,

    Month([Invoice Date]) as Month,

    Date(Monthstart([Invoice Date]), 'D-MMM-YY') as YearMonth,

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

    Dual(Year([Invoice Date]) & '-Q' & Ceil(Month([Invoice Date])/3), Year([Invoice Date]) & Ceil(Month([Invoice Date])/3)) as YearQtr,

    Week([Invoice Date]) as Week

RESIDENT InvoiceDetail;   

-------------------------------------------

but it is showing quarter as

I dont want this "Q". There is no record also associated with "Q" when I click it. I need Q1, Q2, Q3 and Q4.

Please advise.

BR,

SAK

[rwunderlich]

if(len( [Invoice Date]) > 0

,'Q' & Ceil(Month([Invoice Date])/3

,''

) as Quarter

-Rob

[MayilVahanan]

HI

Try like this

If(Len(Trim([Invoice Date])) > 0, 'Q' & Ceil(Month([Invoice Date])/3))

[johnw · Accepted solution]

This may load faster if InvoiceDetail is a large table.

[Calendar]:
LOAD *,
Year([Invoice Date]) as Year,
Month([Invoice Date]) as Month,
Date(Monthstart([Invoice Date]), 'D-MMM-YY') as YearMonth,
'Q' & Ceil(Month([Invoice Date])/3) as Quarter,
Dual(Year([Invoice Date]) & '-Q' & Ceil(Month([Invoice Date])/3), Year([Invoice Date]) & Ceil(Month([Invoice Date])/3)) as YearQtr,
Week([Invoice Date]) as Week
;
LOAD date(fieldvalue('Invoice Date',recno())) as [Invoice Date]
AUTOGENERATE fieldvaluecount('Invoice Date')
;

I think that will also avoid any null invoice dates, so I don't think it would need the Rob/Mayil change in this case.

[CELAMBARASAN]

Hi,

Check whether all the values in invoice date field is in correct date format.

[#6]

Some syntax error I think.. not relaoding...

LOAD DISTINCT

    [Invoice Date],

    Year([Invoice Date]) as Year,

    Month([Invoice Date]) as Month,

    Date(Monthstart([Invoice Date]), 'D-MMM-YY') as YearMonth,

    If(Len(Trim([Invoice Date])) > 0, 'Q' & Ceil(Month([Invoice Date])/3)) as Quarter

    Dual(Year([Invoice Date]) & '-Q' & Ceil(Month([Invoice Date])/3), Year([Invoice Date]) & Ceil(Month([Invoice Date])/3)) as YearQtr,

    Week([Invoice Date]) as Week

RESIDENT InvoiceDetail;

[#7]

As i am new, so i think some error by me in adding your advised code. When I reload, periods not coming up.

LOAD DISTINCT

    [Invoice Date],

    Year([Invoice Date]) as Year,

    Month([Invoice Date]) as Month,

    Date(Monthstart([Invoice Date]), 'D-MMM-YY') as YearMonth,

    if(len( [Invoice Date]) > 0,'Q' & Ceil(Month([Invoice Date])/3,'') as Quarter

    Dual(Year([Invoice Date]) & '-Q' & Ceil(Month([Invoice Date])/3), Year([Invoice Date]) & Ceil(Month([Invoice Date])/3)) as YearQtr,

    Week([Invoice Date]) as Week

RESIDENT InvoiceDetail;

[#8]

Thanks John! It works great.

1) I think Load * is same as Load distinct?

2) As I dont need Year-Month and Year-Quarter, after trial and error in puting comma and semi-colon at right places, I wrote like this and it is working ok.

[Calendar]:

LOAD *,

Year([Invoice Date]) as Year,

Month([Invoice Date]) as Month,

'Q' & Ceil(Month([Invoice Date])/3) as Quarter

;

LOAD date(fieldvalue('Invoice Date',recno())) as [Invoice Date]

AUTOGENERATE fieldvaluecount('Invoice Date')

;

[#9]

Format is ok, need to put fuctionlaity to not to consider nil values or suppress nil values in date coulmn. It's working ok with John code, Rob/mayil code I may not have been able to merge correctly.

[#10]

Dear John/All

I dont have any associated data with selection 2011(Year) and Q2(quarter). But when I click 2011 and then Q2, it brings data assocated with 2012 and Q2. Upon clicking Q2, 2012 gets selected and 2011 deselected. Is there any way that it does not jump to 2012 until selected 2012 and Q2?  Because if a user has selected 2011, it means he does not want to see 2012 at the moment.

Please advise about code to maintain natural flow of viewing and it does not jump to unwanted period.

BR,

SAK