Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
"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.
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;
HI
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
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