How to read months in calendar order--not alphabetical

[#1]

I have been trying to fix this for a while now. I have a variable- vCurrentFiscalMonth (imported from the master calendar) which would result in (JUL), but when I do certain search expressions (vital to my project), it is not working properly because the months are being read in alphabetical order. I have already tried adding an in-line rank and have played around with many different settings under Sort.

Note: I did not create this dashboard or script. I am only making some changes to it.

Here is what part of the script looks like:

Where I have noticed this issue:

The result should give me all the months before (and including) July, but is giving me the ones before in ABC order.

Also, if I try to get the previous month like this, I get no results.

=Sum(Month=(vCurrentFiscalMonth-1))

Help would be greatly appreciated!

[MarcoWedel]

Hi,

you could add the DataAmount field in the resident load and a "noconcatenate" above it to avoid autoconcatenation.

hope this helps

regards

Marco

[#3]

Could you please clarify this a little more for me? Is this taking into consideration the other script I posted above?

[sunny_talwar]

Would you be able to provide your complete script (or at least 3-4 repetitions of the 40 versions you mentioned and then the Left Join from sdfdshgo.xlsx?

[#5]

Of course. I added anything that may be helpful (sorry for the script overload). Here you go:

Prior Yrs tab (extract/most are like this):

CrossTable(Month, DataAmount, 4)
LOAD Version,
Location,
[Line Item],
'No' as Adjustments,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM
[http://.xlsx]
(ooxml, embedded labels, table is [12+0], filters(
Remove(Row, Pos(Top, 1))
));

CrossTable(Month, DataAmount, 4)
LOAD Version,
Location,
[Line Item],
'No' as Adjustments,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM
[http://.xlsx]
(ooxml, embedded labels, table is 2011, filters(
Remove(Row, Pos(Top, 1))
));

FACT:
LOAD *,
'INCOME' as FACT_ID
RESIDENT TEMP;
DROP TABLE TEMP;

LEFT JOIN (FACT)
LOAD Location,
Division,
Region
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

Cash tab (extract):

CrossTable(Month, CashAmount, 4)
LOAD Version,
Region,
[C/F: Line Item],
'No' as Adjustments,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM
[http://jacobsen.sp.textron.com/sites/dept/jacobsenbi/Flat%20File%20Repository/Finance/BalanceSheet_C... 3+9.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Cash Flow]);

CrossTable(Month, CashAmount, 4)
LOAD Version,
Region,
[C/F: Line Item],
'No' as Adjustments,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM
[http://jacobsen.sp.textron.com/sites/dept/jacobsenbi/Flat%20File%20Repository/Finance/BalanceSheet_C... 4+8.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Cash Flow]);

CASH:
LOAD Month,
CashAmount as DataAmount,
[C/F: Line Item] as [Line Item],
Version,
Region,
Adjustments,
'CASH' as FACT_ID
RESIDENT CASH_TEMP;
DROP TABLE CASH_TEMP;

Concatenate(FACT)
LOAD *
RESIDENT CASH;
DROP TABLE CASH;

LOAD [Cash Category] as Category,
[Load Order]
FROM
[http://.xlsx]
(ooxml, embedded labels, table is [Load Order]);

General Build tab (entire tab):

LEFT JOIN (FACT)
LOAD DISTINCT
Month,
[Month Order]
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (FACT)
LOAD DISTINCT 
Month,
Quarter
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

VariableList:

LOAD Variable,
Expression
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

Let vNumRows = NoOfRows('VariableList');

for counter=1 to $(vNumRows)
Let vVariable = Peek('Variable', $(counter) -1, 'VariableList');
Let $(vVariable) = Peek('Expression', $(counter) -1, 'VariableList');
next

DROP TABLE VariableList;

LEFT JOIN (FACT)
LOAD DISTINCT
[Line Item],
[Expense Type],
[Expense Class]
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

Category:
LOAD DISTINCT
[Line Item],
[Hyperion Line Item] as Category
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(Category)
LOAD DISTINCT
[C/F: Line Item] as [Line Item],
[Cash Category] as Category
FROM
[http://.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(FACT)
LOAD DISTINCT *
RESIDENT Category;
DROP TABLE Category;

Order number tab:

// Load the order number of each line item
LEFT JOIN (FACT)
LOAD DISTINCT
[Order Number],
[Line Item]
FROM
[http://.xlsx]
(ooxml, embedded labels, table is LineOrder);

EXIT SCRIPT;

[sunny_talwar]

Everything working fine for you until Exit Script???

[#7]

Yes, the script works until then. Disregard the script after that (I didn't mean to include that last section.

[sunny_talwar]

So then what is not working?

[#9]

Sorry, I wasn't clear enough. I did the resident loads for all the tables and left General Build and Order Number tabs alone.

This format:

Temp43:
CrossTable(Month, DataAmount, 4)
LOAD Version,
Location,
[Line Item],
'No' as Adjustments,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC
FROM
[http:.xlsx]
(ooxml, embedded labels, table is [12+0], filters(
Remove(Row, Pos(Top, 1))
));

Table43:
LOAD Version,
Location,
[Line Item],
'No' as Adjustments,
Month(Date#(Capitalize(Month), 'MMM')) as Month
Resident Temp43;

DROP Table Temp43;

However when I run this version, I get the following errors when it reaches table43 and table44:

   

In the dashboard, the month listbox turns into this:

I tested the search expression my first post has, and it worked IF I used the vCurrentMonth variable. And that only works for the lowercase version of the months which is not linked to the data so doesn't make any changes in the dashboard. If I try the search expression using vCurrentFiscalMonth, I get the results from the third screenshot which takes me back to the same exact problem I had earlier with it not reading in order.

I think I may need to take another approach to this. This has been a bit hard to explain via text, so my apologies for the misunderstandings.  I really have appreciated your help!