I have month and year but cannot get Quarter

[paulyeo11]

Hi All,

Below script from fourm people here work fine :-

SubField([Opportunities Expected Close Date],'-' ,3) as Year,

Num(Month(Date#([Opportunities Expected Close Date], 'MM-DD-YYYY'))) as Month,

Below from me , not working :-

'Q' & ceil(Month(Date#([Opportunities Expected Close Date])),3)/3 as [quarter],

Can some one tell me where i go wrong ?

Paul

[paulyeo11]

Hi Prodosh

[pradosh_thakur]

hoe come you have a date that is 00-00-0000? Exclude that first .and your date is not exactly date. it is a string as its allignment is on the left and not towards the right .

[paulyeo11]

Hi Pradosh

Can you share with me how to exclude those date are zero ? as they are from raw data file. when user does not fill in the date.

Paul

[pradosh_thakur]

you want the date 00-00-000 in the dashboard or not ? IF you want the date but not the quarter then use this as list box expression

if (len(trim(Quarter))>0 ,Quarter)

[paulyeo11]

Hi Sir

Thank you for sharing :-

=if (len(trim(Quarter))>0 ,Quarter)

I don't know where to use the above script ?

By the way , i create below script on the list box :-

=IF([Quarter]='Q1','Q1',

IF([Quarter]='Q2','Q2',

IF([Quarter]='Q3','Q3',

IF([Quarter]='Q4','Q4'

))))

it work fine now.

Paul

[pradosh_thakur]

Hi Paul

in the listbox where we choose fields in hr dropdown there is something called expression. type my expression there, It will be less costly than using four nested if i think.

[paulyeo11]

Hi Pradosh

Thank you , i learn one more thing today

PAul