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

[#1]

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

[Gysbert_Wassenaar]

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

[aveeeeeee7en]

Hi Neil

See the Attachment.

[#4]

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); "

[#5]

Hi,

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

Thank you.

[Anonymous]

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