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!

[antoniotiman]

This could be helpful

Date Format : Interpretation issue

Regards,

Antonio

[oknotsen]

If you use the month() function to get the month out of a date, you will automatically get the month names as defined at the start of the document.

If you sort the month field made with the month() function numerical you will get them sort in the right way.

[sunny_talwar]

You need a resident load after your CrossTable Load to make sure QlikView Understand your Month as a date Month:

Temp:

CrossTable (Month, DateAmount, 4)

LOAD *

FROM ...

Table:

LOAD blahblah,

         Month(Date#(Capitalize(Month), 'MMM')) as Month

Resident Temp;

DROP Table Temp;

[MarcoWedel]

after CrossTable loading the Month field try to replace it (e.g. by join resident loading) with a month field like

Month(Date#(Month,'MMM')) as MonthNum

to get a "real" (i.e. dual) month field.

hope this helps

regards

Marco

[#6]

Thank you for the response! Each tab has multiple loads with the same fields (different sources). I do this for each table for this to work, right?

I just want to clarify I'm doing it correctly:

[sunny_talwar]

Temp looks good, but Table: needs to be this:

Table:

LOAD Version,

          Adjustments,
          Location,

          [Line Item],

          Month(Date#(Capitalize(Month), 'MMM')) as Month

Resident Temp;

DROP Table Temp;

Please post editable format text instead of an image, becomes difficult to retype the whole thing again.

Best,

Sunny

[#8]

Thank you! Sorry about that. I will test this out.

[sunny_talwar]

Oh no, that is fine. It was just a suggestion.

Best,

Sunny

[#10]

Unfortunately, this didn't do the trick for me. I did resident loads for every load (there were over 40 versions of what I posted). What this ended up resulting in was the months just being doubled...the Month listbox ended up with two sets of months (one in all caps and the other in lowercase ('Jan', 'Feb').

I probably should have posted this before but I noticed that the month field is also present in other places in the script..this is in the build tab. I wonder if I should be making changes here instead?

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

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