Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hammermill21
Contributor III

Months in alphabetical order instead of calendar order

Hello,

My months are being pulled in alphabetical order which is then causing me errors when I try and user "MaxString(Month)" and so on. I tried a few things but it's not working in the script end. I have a crosstable so I'm not sure if maybe I can add it in there?

TEMP2:

Crosstable([Facility Temp], [Score Temp], 23) //if any row is added into the excel I need to uyp the numbere here

LOAD

[Record ID],

   AHJ,

    "Date",

    "Month",

  "Year",

    "Prior TJC Survey",

    "Consultants",

     AMP,

    "TOP 10",

    [Top 10 Flag],

    "SAFER  Status",

    Inventory,

    [New  /  Revised],

    DOC,

    FSA,

     "Management Plan",

    [EC-LS-EM],

    [EC-LS-EM Long],

    STANDARD,

    EP#,

    [CHAPTER EP#],

    [ELEMENT OF PERFORMANCE],

    "UMHC SYSTEM",

  //  Chapter,

   

    "UMHC SCCC",

    "UHealth Tower",

    "UMHC BPEI",

    "UMHC LFMC",

    "UMHC Radiology",

    "UMHC Applebaum",

    "UMHC Ear Institute",

    "UMHC Pain Clinic",

    "UMHC Safie",

    "UMHC IR Clinic",

    "UMHC Coral Spring",

    "UMHC Deerfield",

    "UMHC Hollywood",

    "UMHC Kendall",

    "UMHC Plantation",

    "UMHC Marlin Park"

RESIDENT TEMP;

Drop Table TEMP;

[ALL DATA]:

LOAD RecNo() as ID,

Replace([Facility Temp],'UMHC ','') as Facility,

    //If([Score Temp]=0,'NULL',[Score Temp]) as Score,

    If([Score Temp]=0,'N/A',[Score Temp]) as Score,

*

Resident TEMP2;

Drop Table TEMP2;

Drop Fields [Facility Temp], [Score Temp];

[UHealth Facility]:

LOAD

    "UHealth Facility" as Facility,

    Latitude,

    Longitude,

    GeoMakePoint(Latitude,Longitude) as GeoPoint,

    Address,

    City,

    State,

    ZIP

FROM [lib://UHealth Facility (uhealth_54171224)/UHealth Facility.xlsx]

(ooxml, embedded labels, table is [UHealth Facility])

Where NOT IsNull(Latitude);

21 Replies

Re: Months in alphabetical order instead of calendar order

Where is the month field in this script? I couldn't find it by eye balling it

Also, did you ever look at my last response here?Data Containing Anything other than 1 or 0 will not Total

hammermill21
Contributor III

Re: Months in alphabetical order instead of calendar order

Hey Sunny,

I'm starting to feel like maybe you should be charging me a fee for all the help you provide!! lol

So within my script, the month field is also here:

Capture_month.PNG

The way the data is being collected I have to pull in the excel sheets separately so you see each month like this:

Capture_breakdown.PNG

Anyhow, that's why I'm wondering if it's something I can add to the crosstable to edit the way the months are showing. From my raw data, all you get is the Month as in the Month name.

Make sense?

Re: Months in alphabetical order instead of calendar order

Change Month to this

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

hammermill21
Contributor III

Re: Months in alphabetical order instead of calendar order

In the crosstable section or in each data tab (for each month)?

Re: Months in alphabetical order instead of calendar order

Probably do it on the crosstable if all that is doing is doing a crosstable on the concatenation of the monthly loads

hammermill21
Contributor III

Re: Months in alphabetical order instead of calendar order

I had tried that and it didn't work but it was my fault, now it's working! THANK YOU

I have another question though, with this I use Min and Max String for two months to calculate differences, so something like this:

Count({<[UMHC SYSTEM]={'100%'},[EC-LS-EM]= {'EC'}, Month = {"$(=MinString(Month))"}>} [UMHC SYSTEM])/16

So now it picks up the min month as fed and the max as April which is correct when using that, but what if I want to use LAST month and this current month? How could I do that?

hammermill21
Contributor III

Re: Months in alphabetical order instead of calendar order

Hey Sunny,

I got this working but now it picks up the min month as fed and the max as April which is correct when using that, but what if I want to use LAST month and this current month? How could I do that?


Would you have any ideas?


Thanks!

Re: Months in alphabetical order instead of calendar order

Not sure I follow...

Re: Months in alphabetical order instead of calendar order

Cross Table works from single table table. I your case, Seems there has multi tabs involved. Can you point out that point specifically where and what you are trying.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)