Sorting Days of Week in Sunday, Monday, ... , Saturday

[Cognos_Jan2017]

Having trouble writing a Query Calculation which I would use to sort the "Day of Week"
Query Item as a Crosstab Column.

I have tried CASE and IF attempts, but it doesn't like my syntax.  The Query Item
is "Day of Week"

These don't work ...
CASE
WHEN [Day of Week] = 'Sunday' Then 1
WHEN [Day of Week] = 'Monday' Then 2
WHEN [Day of Week] = 'Tuesday' Then 3
WHEN [Day of Week] = 'Wednesday' Then 4
WHEN [Day of Week] = 'Thursday' Then 5
WHEN [Day of Week] = 'Friday' Then 6
WHEN [Day of Week] = 'Saturday' Then 7
END

... My background is Visual Basic/ VBA ...
If [Day of Week] = 'Sunday' Then 1
ElseIf [Day of Week] = 'Monday' Then 2
ElseIf [Day of Week] = 'Tuesday' Ten 3
ElseIf [Day of Week] = 'Wednesday' Then 4
ElseIf [Day of Week] = 'Thursday' Then 5
ElseIf [Day of Week] = 'Friday' Then 6
Else [Day of Week] = 'Saturday' Then 7
End If

Any assistance welcomed.  TIA, Bob

[New_Guy]

Hi,
Quick solution is to Create a data Item for each day and place them one after the other as you want as there are only 7 of them and they wont change. I dont think we can arrange columns with a sort function.
Good luck,
New Guy.

[Cognos_Jan2017]

We are using Cognos within a product ProcessMAP.  In that a Field named something like "Month Name" is used as a column in a crosstab (nested under a "Fiscal Year" Query Item).  That Query Item "Month Name" is selected.  Then find a Field in the Model named "Month #" is used to sort the "Month Name"s in the Crosstab column.  Works great.

How would creating those 7 Data Items work when added to the Query for the Crosstab?  When you say "place them one after the other" ... how?  We have two Query Items Nested in the crosstab columns ... "Date of Incident" above "Day of Week" (the Sunday thru Saturday values).

TIA, Bob

[AnalyticsWithJay]

These don't work ...
CASE
WHEN [Day of Week] = 'Sunday' Then 1
WHEN [Day of Week] = 'Monday' Then 2
WHEN [Day of Week] = 'Tuesday' Then 3
WHEN [Day of Week] = 'Wednesday' Then 4
WHEN [Day of Week] = 'Thursday' Then 5
WHEN [Day of Week] = 'Friday' Then 6
WHEN [Day of Week] = 'Saturday' Then 7
END

This exact syntax works for me. I suspect you may be seeing another error from the query. What's the syntax error you're receiving?

Try taking this syntax and placing it in a new query with Day of Week. Can you see tabular data?

[Cognos_Jan2017]

In existing Query, and, in a new Query with only Day of Week, it gives long error (can't copy the error msg, but ....) ...
QE-DEF-0459 CCLexception
QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[Day of Week]' that does not exist.

The above 3 lines repeat 6 more times which means it didn't like any of the 6 "WHEN"s.

Ideas?  TIA, Bob

[Cognos_Jan2017]

This makes it work ... replace [] with ' '
CASE
WHEN 'Day of Week' = 'Sunday' Then 1
WHEN 'Day of Week' = 'Monday' Then 2
WHEN 'Day of Week' = 'Tuesday' Then 3
WHEN 'Day of Week' = 'Wednesday' Then 4
WHEN 'Day of Week' = 'Thursday' Then 5
WHEN 'Day of Week' = 'Friday' Then 6
WHEN 'Day of Week' = 'Saturday' Then 7
END

Now, I don't know how to utilize the 1,2, ..., 7 to sort the Day of Week entries in the crosstab column.
Can't see the Query Calculation Data Item in MetaData to Sort by.

Easy solution ... I hope?

TIA, Bob

[bberryhill]

Click the Cross Tab Member that contains your day description.
In the properties section you should see a Sorting option (first one under "Data").
Click the ellipses.  Your "day number" data item should appear - drag it into the sort list.

Hope that helps.

[Cognos_Jan2017]

It should have worked, but it didn't.

Tried both 1,2, ..., 7
and
A,B, ..., G

Going home, but back on cognoise.com Noon tomorrow.

Could there be some setting preventing the sort?

TIA, Bob

[Lynn]

QE-DEF-0359 The query contains a reference to at least one object '[Day of Week]' that does not exist.

I am assuming you have a relational rather than dimensional package? There can be important differences depending on the type of source you have.

This means your expression has no idea what [Day of Week] is. If your case query item expression is not fully qualified then it expects to find [Day of Week] already in the query as another query item on its own. Alternatively, fully qualify the reference for [Day of Week] such as [YourPackage].[Your Query Subject].[Day of Week].

This makes it work ... replace [] with ' '
CASE
WHEN 'Day of Week' = 'Sunday' Then 1
WHEN 'Day of Week' = 'Monday' Then 2
WHEN 'Day of Week' = 'Tuesday' Then 3
WHEN 'Day of Week' = 'Wednesday' Then 4
WHEN 'Day of Week' = 'Thursday' Then 5
WHEN 'Day of Week' = 'Friday' Then 6
WHEN 'Day of Week' = 'Saturday' Then 7
END

Your statement above might "work" only in the sense that you don't get a syntax error. Otherwise it is meaningless. You could just as easily write

Code Select Expand


CASE
WHEN 'Blue' = 'Orange' then 7
WHEN 'Alpha' = 'Omega' then 42
END


It would work exactly the same as what you've written and also utterly nonsensical.

[New_Guy]

Hi,
Can you share the xml of the report, just with the columns in the crosstab? change the names if you want to hide the actual names.
Thanks
New Guy

[Cognos_Jan2017]

Reply to Lynn ...
You are correct, and this is a relational package.  There were NO syntax errors, but NO "Then" results.  That's why the Sort
recommendation from bberryhill didn't work.

I will fully qualify the reference, try that, and report back shortly.

Thank you, Bob

[Cognos_Jan2017]

To EVERYONE who helped in this discussion ... THANK you.

After fully qualifying the Query Item, ALL is BEAUTIFUL !!1

Everyone have a good weekend, Bob

[Invisi]

The afterburner... Are you pulling your Day of Week names from a date dimension? Is this date dimension lacking the day or week numbers? Usually a robust date dimension has exactly these numbers, saving you time in crafting something in your reporting tool (e.g. Cognos).

[Cognos_Jan2017]

The relational design has 'Incident Date of Week" NOT as a "measure".

The discussion here allows a Report Author to write a Query calculation to enable sorting
on Day of Week (7 days) and Months (12) by using that to sort.

[Invisi]

I don't speak about a measure? I speak about date as a dimension, where day of week number is an attribute?