topic Re: Year, Quarter, Month Selection List Box in QlikView

Year, Quarter, Month Selection List Box

Thu, 13 Dec 2012 14:52:48 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

Re: Year, Quarter, Month Selection List Box

rwunderlich — Thu, 13 Dec 2012 15:27:02 GMT

if(len( [Invoice Date]) > 0

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

,''

) as Quarter

-Rob

Re: Year, Quarter, Month Selection List Box

MayilVahanan — Thu, 13 Dec 2012 16:34:19 GMT

Try like this

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

Re: Year, Quarter, Month Selection List Box

johnw — Thu, 13 Dec 2012 20:14:39 GMT

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.

Re: Year, Quarter, Month Selection List Box

CELAMBARASAN — Thu, 13 Dec 2012 20:25:46 GMT

Hi,

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

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 08:33:44 GMT

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;

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 08:35:18 GMT

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;

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 08:40:50 GMT

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')

;

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 08:43:50 GMT

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.

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 11:49:04 GMT

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

Re: Year, Quarter, Month Selection List Box

johnw — Fri, 14 Dec 2012 15:48:49 GMT

"LOAD *" means "load all fields". (Edit: It's not the same as a load distinct, and you don't need a load distinct here because the fieldvalue() approach already gives you distinct values.) A load statement with another load immediately following is called a "preceding load". What it does is load all rows from the load statement beneath it, and the "LOAD *" tells it to load all fields from it as well. In this case, there's only one field so I could have listed it, but I'm in the "LOAD *" habit in preceding loads unless there's a reason to restrict which fields I'm getting. It's similar to an SQL SELECT *, though that's something I'd recommend almost never doing due to the extra overhead on your DBMS and the way it opens you up to problems caused by new fields added to the database.

Your script revision looks fine to me. I nearly always use actual dates formatted as Year, Month and Quarter, as it allows me to use all of QlikView's date functions on them. But it's probably better to do what you've done if you're intending to do things like compare the months of one year to the months of the next, which may well be what you intend.

The (overly) simple answer to your question is no, I don't think there's any good way to keep QlikView from deselecting things that don't match the new selection you're making - that's just how QlikView behaves. Some users would prefer it came back and said "no matching data" or something, but that's just not how QlikView behaves. That said, Q2 should have been grayed out when you selected 2011. That MEANS there's no matching data, and it's the user's signal that if they DO select Q2, it's going to change some of their other selections so that Q2 DOES match some data. If the users really want to lock in what they've selected so far, they can use the lock function, but it's really easier if they just learn to recognize what gray selections mean.

For this specific problem, though, one solution would be to generate a calendar with all dates in it instead of just all dates with data. That's actually what I typically do (well, load my calendar from a QVD that has all dates). So you'd let the users select 2011 Q2, and you'd just get no associated invoice data instead of wiping out 2011 when they select Q2. There are ways to pull min and max dates out of your data set, then generate everything between. But more typically, I know the date range that I'm trying to show in an application, such as all data from the beginning of three years ago through today. In that case, you can generate your dates with an AUTOGENERATE statement and not worry about when your actual data starts and ends.

Re: Year, Quarter, Month Selection List Box

Fri, 14 Dec 2012 17:50:02 GMT

It laoding now. I am able to write correct syntax now. Just to learn, I am also trying your suggestion but when it shows up ok all year, quarters (Q1, Q2, Q3, Q4) and months BUT when I click any year, quarter or month data in tables or chart not is filtering up. Code is below for your kind advise

[Calendar]:

LOAD Distinct

Year([Invoice Date]) as Year,

Month([Invoice Date]) as Month,

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

Resident InvoiceDetail;

Re: Year, Quarter, Month Selection List Box

MayilVahanan — Fri, 14 Dec 2012 17:56:38 GMT

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, // missed comma here and one closing braces.

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;

And follows the John Witherspoon suggesion

Re: Year, Quarter, Month Selection List Box

preminqlik — Wed, 17 Jul 2013 12:31:22 GMT

Above all suggestions are good

if you want to do in new way, try this

if(left(QuarterName(TempDate),7)='Jan-Mar','Q1',

if(left(QuarterName(TempDate),7)='Apr-Jun','Q2',

if(left(QuarterName(TempDate),7)='Jul-Sep','Q3','Q4'))) as Quarter

(---> if you want to create financial quarter just create financial year and change according to month names in above link .)

-premhas