Months Sorting Problem

[#1]

Hi All,

I have two issues developing a deashboard.

Issue #1
------------
I have a table in which I have data data based on years and months. My structure is the following.

1. Year
2. Months
3. Call_time
4. Call_party

I have 2008 and 2009 year data and in the months field I have data like "Jan", "Feb", "Mar", "Apr", "May", "Jun","Jul","Aug","Sep" ....and I am using MONTHS column as a group (dimension) so on...problem is that I have sorting order problems in my bar chart...It is not sorted properly...the above sequence is correct sequence rather its like "Apr", "Jul", "Mar", "May" and so on...weird.....can u please suggest what to do and how to solve this ...here is my loading script.

QUALIFY *;
UNQUALIFY YEARS, LDI_CD, TERM_CD, SOURCE;

SQL SELECT *
FROM LDIOPERATORS;;

SQL SELECT *
FROM TERMOPERATORS;;

SQL SELECT *
FROM IPDATASUM_VIEW;;

SQL SELECT *
FROM VW_PRE_PROBE_SUMMARY;

SQL SELECT *
FROM VW_INCOMING;

Issue # 2
--------------

we are a telecommunication company and its our testing of dashboards so in our database we created summary tables and we get data from capturing device which we process and then add that particular month data into the tables which updates the summary tables. I noticed that it sometimes skips a month data in chart like months are there except JUNE...OR SOMETIME March....what can be the problem?

Please answer as I am stuck.

Thanks

[#2]

for #1 I would suggest to use numerical for months instead. It pretty much depends on what purpose the output is.

just cast the month name via num() (and prepend the months < 10 with 0) to make 01 02 ... 12. Sortable and readable. But if you are in for some pretty things that's probably not a good solution.

[#3]

I would use a number for the month. Use the formula below:

MonthNum = (index('Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec', Month) - 1 ) / 4 + 1

Use it in an expression field, then sort that, or use it in a load statement as:

(index('Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec', Month) - 1 ) / 4 + 1 as MonthNum

You may like to add it as a preceding load to your SQL statements.

[prieper]

Hi,

sorting makes most sense on numerical values, display or formatting is something completely different. Thus you may use the above or - especially if viewing multiple years - use MonthName(YourDate)

HTH

Peter

[#5]

IVANG thank you for the reply...tell me one thing...can u guide me where to put the formula you have mentioned. I mean should I put it in Chart->Properties->Sort->Expression ? or somewhere else...please guide

MonthNum = (index('Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec', Month) - 1 ) / 4 + 1

If I would go for LOAD statement then how to write your code or use in the following load script. I want to make change in "IPDATASUM_VIEW" TABLE..can u guide? thanks

Load Script
----------------------------------------------------------------------

ODBC CONNECT TO [xxxx;DBQ=TELSU];

QUALIFY *;
UNQUALIFY YEARS, LDI_CD, TERM_CD, SOURCE;

SQL SELECT *
FROM LDIOPERATORS;;

SQL SELECT *
FROM TERMOPERATORS;;

SQL SELECT *
FROM IPDATASUM_VIEW;;

SQL SELECT *
FROM VW_PRE_PROBE_SUMMARY;

SQL SELECT *
FROM VW_INCOMING;

[#6]

Ohhhhh I just figured it out...isnt like this?

LOAD
YEARS,
MONTHS,
(index('Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec',MONTHS) - 1 ) / 4 + 1 as MonthNum,
DAYSNUM,
HOURS,
TOTAL_TIME,
SOURCE
;
SQL SELECT *
FROM "IPDATASUM_VIEW";

and then use MONTHnum in expression? ...

[#7 · Accepted solution]

Yes, you seem to have got it!

Adding the LOAD section puts the extra field in your table, which you then add to your chart and sort by that.

If you want to use the other way I suggested, use the way you have given ( i.e. sort by an expression and put the formula in the box ) or create a new expression using that formula, and sort on the new expression.