Months in alphabetical order instead of calendar order

[hammermill21]

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

[hammermill21]

Hi Anil,

So I have a KPI that is showing a month to month comparison and to do so I use =MaxString(month) and =MinString(Month) which works fine. What I want to know if there is a way I can trigger it to point at the last month because MinString is selecting Feb since that is the oldest month I have data for. So instead of Feb I want it to select April since it's last month and I would now be comparing April to May.

[hammermill21]

Hey Sunny,

So I have a KPI that is showing a month to month comparison and to do so I use =MaxString(month) and =MinString(Month) which works fine. What I want to know if there is a way I can trigger it to point at the last month because MinString is selecting Feb since that is the oldest month I have data for. So instead of Feb I want it to select April since it's last month and I would now be comparing April to May.

[sunny_talwar]

May be try this to get April

=MaxString(AddMonths(Date#(Month, 'MMM'), -1))

[hammermill21]

That doesn't seem to work.

RIght now for min month I have this:

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

And when I added your suggestion but as MinString it doesn't seem to work.

[sunny_talwar]

Why as MinString?

[hammermill21]

My mistake, I entered as you suggested. I was answering two questions at once and mixed it up.

What I tried is this:

(Count({<[UMHC SYSTEM]={'100%'},[EC-LS-EM]= {'EC'}, Month = {"$=MaxString(AddMonths(Date#(Month, 'MMM'), -1))"}>} [UMHC SYSTEM])/16)

/

(Count({<[EC-LS-EM]= {'EC'}>} EP#) / (16 * count({<Date={'<=$(=today())'}>}distinct Month)))

To get last month but that doesn't work, I for sure wrote the expression out incorrectly but not sure where because I don't get errors, I just get a 0%.

[sunny_talwar]

Can you create a filter object like this

Num(Month) to see if it gives you 1, 2, 3... etc? This is to check if Month is a dual field or not...

[jduenyas]

MinString(Month) and MaxString(Month) will refer to the first and last months you have selected in your Calendar selections or if no Calendar selections then the first month loaded and the last month loaded.

To compare May and April (current month and previous month)

use set analysis {Month={">=$(=AddMonths(today(),-1))<=$(=Month(Today())) }  }

This will compare Present month (May as of today) and Previous month (April as of today)

[hammermill21]

Hey Sunny,

No it's not that doesn't give me the 1,2,3...

[sunny_talwar]

Can you try one of these

1)

(Count({<[UMHC SYSTEM]={'100%'},[EC-LS-EM]= {'EC'}, Month = {"$(=MaxString(AddMonths(Date#(Month, 'MMM'), -1)))"}>} [UMHC SYSTEM])/16)

/

(Count({<[EC-LS-EM]= {'EC'}>} EP#) / (16 * count({<Date={'<=$(=today())'}>}DISTINCT Month)))

2)

(Count({<[UMHC SYSTEM]={'100%'},[EC-LS-EM]= {'EC'}, Month = {"$(=Text(MaxString(AddMonths(Date#(Month, 'MMM'), -1))))"}>} [UMHC SYSTEM])/16)

/

(Count({<[EC-LS-EM]= {'EC'}>} EP#) / (16 * count({<Date={'<=$(=today())'}>}DISTINCT Month)))