on 04-10-2013 9:22 PM
Greetings,
What is the best way to combine and display the following logic in a crosstab report?
Online: if Section Number = 65; 65A; S165; S265
Hybrid: if Section Number = 75; 75A; S175; S175A
Day: if start time < 5:00pm
Night: if start time =>5:00pm
Other: no start time
I have the following formulas and would like to know if I can somehow combine these and/or find a better way to accomplish the above.
Hybrid = [Section Number] InList (“65”; "65A”; "S165”; "S265")
Online = [Section Number] InList( "75" ; "75A" ; "S175" ; "S175A")
Time =If FormatDate( [Start Time] ;"HHmm") >= "1700" Then "Night"
ElseIf( IsNull( [Start Time]) ) Then "Other" Else "Day"
TIA,
Rakesh,
Thanks for the reply and per your suggestion attached is a sample screenshot.
I created the following two dimension variables (Tyepe1 and Type2):
Type1 = If( [Section Number] InList("75" ; "75A" ; "S175" ; "S175A")) Then "Online" ElseIf [Section Number] InList (("65"; "65A"; "S165"; "S265") Then "Hybrid"
Type2 = If FormatDate ([Meeting Start Time] ; "HHmm") >= "1700" Then "Evening" ElseIf(IsNull ([Meeting Start Time]) ) Then "Other" Else "Day"
The crosstab report no longer displays #Multivalue. However, while in a vertical report, I noticed wherever there are multiple start time for a given course/section number, it shows both Hybrid and other, or online and other.
The goal is to base the logic on section number and start time and determine if it's online, hybrid, day or evening. Is there any way to combine both Type1 & Type2 create one dimension variable?
In the attached example based on section number S165 this should display only Hybrid
Regards,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, Rakesh,
That was my initial thought. But further clarifying, following is the requirement:
First, take a look at the section number & determine if they are online/hybrid, Some of these hybrid/online classes may have start time, but in this case, we are not interested in displaying records with start time (day/evening). Only display record online/hybrid.
Next, we check the section number again and if they do not fall under online/hybrid we will look at the start time and determine if the class is day/evening.
Lastly, if the class doesn't qualify the above:
a. not online/hybrid,
b. doesn't have start time) then we display "Other"
Please let me know if this clarifies.
Regards,
What happens when you include them all in a single series of If.Else Statement?? Have you tried it??
Is that when you get #MULTIVALUE?
IF( [Section Number] InList (“65”; "65A”; "S165”; "S265") ) THEN "HYBRID" ELSEIF ( [Section Number] InList( "75" ; "75A" ; "S175" ; "S175A")) THEN "ONLINE" ELSEIF(FormatDate( [Start Time] ;"HHmm") >= "1700") THEN "NIGHT" ELSEIF( IsNull( [Start Time]) ) Then "OTHER" Else "Day"
Rakesh,
I created a new variable dimension (Type) and combined my formulas into this single variable and it is
working (in vertical table) For some reasons, I cannot get this to work in crosstab. When I drag Type across my crosstab report, the numbers are not correct (e.g. total are the same across report etc.)
It’s probably easier to take a look at a screenshot to better understand what is going on. Is there a way I can attach this as a word document?
Regards,
Hey! I believe you had asked this question sometime back. This is not yet resolved?
Can you please drag the three variables (Hybrid, Online and Time) into a table in your WebI report and take a screenshot? Just for us to see how the variable values comes up...I believe there will be multiple times for one section which might be causing the issue.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
61 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.