Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have created a dashboard which has a date field in it which the user can select the date and all the information and charts change. However, the user wants to be able to select, year , quarter and month in stead of a specific date. How can i set this up?
Thanks
Laura
Year(date) as Year,
Month(date) as Month,
'Q' & Ceil(date/3) as Quarter
Here are another two to get a concatenation of the month and year...
date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,
MonthName(Date) as monthYear
This is a bit more than you're asking for, but I create a calendar QVD and then load it in the QVWs. That way, I only maintain my date logic in one place, and it helps standardize formats. Here's some of the relevant code (there are a lot more fields than this). I almost never use the "... Only" fields. I almost always prefer data that includes the year in it. An exception would be for year on year comparisons, which are more easily done with the year split out.
[Calendar]:
LOAD *
,dual('Q' & "Quarter Only" & ' ' & "Year Only",quarterstart("Date")) as "Quarter"
;
LOAD *
,ceil("Month Only"/3) as "Quarter Only"
;
LOAD *
,date(monthstart("Date"),'MMM YYYY') as "Month"
,date(yearstart("Date"),'YYYY') as "Year"
,month("Date") as "Month Only"
,year("Date") as "Year Only"
;
LOAD date(today() + 10000 - recno()) as Date
AUTOGENERATE 20000
;
Where abouts do you put that?
Sorry i am really confused :s
I have a Dates Field which has dates like 15/05/2010
i want to break that field down into three selection boxes. Year, Month and Quarter.
Do i do this within the load script or in the selection box expression tab
Do it in the script. Use this peice of code.
Date_temp:
load
today()-recno()+1 as Date
autogenerate(today()-'2007-12-31');
Load
Date as myDateField, - (myDateField is your current date field)
Year(Date) as Year,
Month(Date) as Month,
'Q' & Ceil(date/3) as Quarter,
date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,
MonthName(Date) as monthYear
Resident Date_temp
Where abouts in the script. I put it at the bottom and its gets an error. It doesn't like my date field name.
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