topic Re: How to create Quarter and Month Selection Boxes in QlikView

How to create Quarter and Month Selection Boxes

Wed, 16 Nov 2011 16:27:06 GMT

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

How to create Quarter and Month Selection Boxes

Wed, 16 Nov 2011 16:40:59 GMT

Year(date) as Year,

Month(date) as Month,

'Q' & Ceil(date/3) as Quarter

How to create Quarter and Month Selection Boxes

Wed, 16 Nov 2011 17:43:19 GMT

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

Re: How to create Quarter and Month Selection Boxes

johnw — Wed, 16 Nov 2011 18:03:17 GMT

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
;

Re: How to create Quarter and Month Selection Boxes

Thu, 17 Nov 2011 12:30:48 GMT

Where abouts do you put that?

Re: How to create Quarter and Month Selection Boxes

Thu, 17 Nov 2011 12:34:13 GMT

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

How to create Quarter and Month Selection Boxes

Thu, 17 Nov 2011 12:39:44 GMT

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

How to create Quarter and Month Selection Boxes

Tue, 06 Dec 2011 16:30:35 GMT

Where abouts in the script. I put it at the bottom and its gets an error. It doesn't like my date field name.

Re: How to create Quarter and Month Selection Boxes

Thu, 13 Dec 2012 14:53:50 GMT

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