combining formulas

[Former Member]

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,

[Former Member]

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, 

[former_member189638]

But S165 in your example has start time as 8:00 a.m. So, it should be Displayed as Day  ..right???

[Former Member]

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,

[former_member189638]

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"

[Former Member]

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,

[former_member189638]

In the reply box there is an option to Use Advanced Editor on top where you can attach files..But it won't take more than 1MB file and also doc is not supported...you can just put the screenshot here...that will be helpful as well.

[Former Member]

Thanks Rakesh

Please let me know if the following helps:

Regards,

[former_member189638]

Hmm..That is bit weird. Ideally it should distribute the Grade across Type Variable dimension.

Did you try creating a Variable measure [Grade] ForEach [Type] and use it in your table.

Alos hope [Type] is a Dim Variable and not Detailed Object Variable.

[Former Member]

As you can see in the attached screenshot , I've variable measures for each [Grade] and
Yes, [Type] is a Dim Variable (not a detailed Object)

By the way, instead of as attachments, I attempted to copy/paste just the image w/o any extras w/o any success.

Regards,

[former_member189638]

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.