topic Re: how to customize a selection list in relation to an academic year?? in QlikView

how to customize a selection list in relation to an academic year??

Mon, 24 Feb 2014 17:29:31 GMT

Hi, in my database I have years and months and I would create a list of selections per academic year

for example in my database i have 2012, 2013, 2014 and i would sort from October 2012 to September 2013 and from October 2013 to September 2014 and my list will display 2012/2013, 2013/2014.

how i can do it? thx

Re: how to customize a selection list in relation to an academic year??

Gysbert_Wassenaar — Mon, 24 Feb 2014 17:39:24 GMT

Create a couple of extra fields for academic year and month. See this blog post: Fiscal Year.

Re: how to customize a selection list in relation to an academic year??

aveeeeeee7en — Mon, 24 Feb 2014 18:12:24 GMT

Hi Neil

See the Attachment.

Re: how to customize a selection list in relation to an academic year??

Sat, 15 Mar 2014 21:36:43 GMT

Hi, thank you but i have another problem !

I followed this tuto but this script takes only the data of the first (*.xslx) file.

Can you help me please?

This is my script:

" Set vFM = 10 ;

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual academic year

Dual(Month, fMonth) as FMonth, // Dual academic month

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric academic year

Mod(Month-$(vFM), 12)+1 as fMonth,

Load Year([Date 1er contact]) as Year, // Your standard master calendar

Month([Date 1er contact]) as Month,

FileName() as FileName,

[Date 1er contact],

FROM C:\Prospects\Prospects_*.xlsx

(ooxml, embedded labels); "

Re: how to customize a selection list in relation to an academic year??

Mon, 02 Jun 2014 01:20:56 GMT

Hi,

Please can you send me a script? I have QlikView Personal Edition. i can't open this document.

Thank you.

Re: Re: how to customize a selection list in relation to an academic year??

Anonymous — Mon, 02 Jun 2014 01:53:19 GMT

Hello.

Here is a clean up of the code. Hope it works.

Set vFM = 10 ;

Prospects:
Load
  Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual academic year
  Dual(Month, fMonth)              as FMonth, // Dual academic month
  *;
Load
  Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric academic year
  Mod(Month-$(vFM), 12)+1        as fMonth, // Numeric academic month
  *;
Load
  *,
  Year([Date 1er contact])  as Year, // Your standard master year
  Month([Date 1er contact]) as Month // Your standard master month
From C:\Prospects\Prospects_*.xlsx
(ooxml, embedded labels);