Qlikview Bug? I really can't sort Fiscal Month Calendar! Help me!

[#1]

Hi guys,

I'm with a problem to sort a list box with month, where this month is a month calendar.

The fiscal year here starts on Abril to March.

When I using the list box and sorting by expression, could be FiscalPeriod or sum(all FiscalPeriod), the wlk works fine.

I mean:

Fiscal Month, Fiscal Period

Apr   1

May  2

Jun   3

...

Mar  12

But, the active sheet needs to select the last 3 months.

I did it by a macro using a trigger on active sheet.

I don't know why!! Qlikview works fine for few minutes and after sort automatically from Jan to Dec.

I've search a lot of thing here in forum, but anyone works fine to me.

Please, I need your help.

I'm sending a test qlik for your analysis.

Thank you,

Rodrigo.

[stigchel]

'all' is not a command, 'Total' is, so use sum(TOTAL FiscalPeriod) ?

[Gethyn]

Hi Rodrigo,

Here is the code for how I have created a master calender for financial years.

MonthMap:
Mapping LOAD * INLINE [
    PERIOD_NUM, Month
    1, April
    2, May
    3, June
    4, July
    5, August
    6, September
    7, October
    8, November
    9, December
    10, January
    11, February
    12, March
    13, Adjusting Period
];
PeriodsTemp:
LOAD
          "PERIOD_NAME",
          right("PERIOD_NAME",7)&'-'&left("PERIOD_NAME",3) as Period,
    "PERIOD_SET_NAME",
    "START_DATE",
    "END_DATE",
    "PERIOD_TYPE",
    "PERIOD_YEAR",
    num("PERIOD_YEAR")-1&'/'&right(num("PERIOD_YEAR"),2) as [Financial Year],
    "PERIOD_NUM",
    ApplyMap('MonthMap', PERIOD_NUM) as Month,
    "QUARTER_NUM",
    'Q'&"QUARTER_NUM" as [Quarter Name];
SQL SELECT *
FROM APPS."GL_PERIODS_V"
Where "PERIOD_TYPE" = '21';
Store PeriodsTemp into PeriodsTemp.qvd;
Drop Table PeriodsTemp;
LET varMinDate = num('01/04/2007');
LET varMaxDate = num('31/03/2015');
Dates:
LOAD date($(varMinDate)+recno()-1) as Date
AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;
Periods:
          LOAD 
                    "PERIOD_NAME",
                    Period,
              "PERIOD_SET_NAME",
              "START_DATE",
              "START_DATE" + IterNo() -1 as Date,
              "END_DATE",
              "PERIOD_TYPE",
              "PERIOD_YEAR",
              "Financial Year",
              "PERIOD_NUM",
              Month,
              "QUARTER_NUM",
              "Quarter Name"
          FROM
          PeriodsTemp.qvd (qvd)
          While "START_DATE" + IterNo() -1 <= "END_DATE";
          Store Periods into MasterCalendar.qvd;
Drop Table Periods;

The APPS."GL_PERIODS_V table is from our Oracle database and defines the start and end dates for each financial period and also determines which financial year and quarter that period is in.

The auto generate statement fills in all the missing dates between each start and end date.

You should be able to achieve the same end result by using an Excel spread sheet instead of the APPS."GL_PERIODS_V tabe to define the period start and end dates.

Hope this helps,

Gethyn.

[#4]

Hi Stigchel,

I'd trid with Total as well, and set analysis, min(sum..) etc.. and it does not work fine for me...

Thanks,

Rodrigo.

[#5]

Hi Gethyn,

Thanks for your code.

I'll try to update my script.... but I think it will be the same...

I'll try...

Thank you.

Regards,

Rodrigo.