Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field as below which works out the fiscal quarter :
'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3) as Fiscal_Quarter
The values returned ie Q1,Q2,Q3,Q4 currently appear in a jumbled up order ( due to the 'Q' ) even though it has been sorted in ascending order. How do I get the values to appear in the correct ascending order ? Do I need to modify the script line or would it be a change in the 'Sort by' options ?
Thanks
Dual(
'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3)
, Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3
as Fiscal_Quarter
Will give you a Dual field that use Q1, Q2, Q3, Q4 as display values in the UI and sort according to the Quarter number in list boxes and other places...
Create a Dual field instead like:
Load
Dual(Fiscal_Quarter, QuarterStart($vDate)) as DFiscal_Quarter; // preceding load
Load
'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3) as Fiscal_Quarter
From <>;
And then use this new dual field in your chart which would sort correctly.
Dual(
'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3)
, Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3
as Fiscal_Quarter
Will give you a Dual field that use Q1, Q2, Q3, Q4 as display values in the UI and sort according to the Quarter number in list boxes and other places...
Worked perfect with the dual function :
Similarly how would this work if using the pick match function ? This time for fiscal year :
pick( WildMatch((If(Month($(vDate)) > 3, Year($(vDate))+1, Year($(vDate)))),'*2013*','*2014*','*2015*', ),
'FY 13','FY 14','FY 15') as FYear
I would advice you to have a look at this document:
Or have a look at the QlikView Components QVC which gives you all these "calculations" for free ...