distinct count

[Former Member]

Greetings,

I used the following formula to determine if a given course is (day, evening, etc.) .which works fine in a vertical report format, but gives wrong count (doubles) when in crosstab report.  Plese see example below.

Var1

= If([Section Number]) InList( "75" ; "75A" ; "75B" ; "75C" ; "85" ; "99" ; "S175" ; "S175A" ; "S175B" ; "S199" ; "S275" ; "S275A" ; "S275B" ; "S285" )
Then "Online" ElseIf [Section Number] InList ( "65" ; "65A" ; "S165" ; "S265" ) Then "Hybrid" ElseIf FormatDate ([Meeting Start Time] ; "HHmm") >=
"1700" Then "Evening" ElseIf(IsNull ([Meeting Start Time]) ) Then "Other" Else "Day"

Var2

= Min([Total Enrolled Distinct]) In ([Section ID] ; [Section Number] ; [Term] ; [Course Name] ; [Meeting Start Time]

Course      Term          Section #    Start time       Type      Total

P-099        2012-WI      05              1:00 PM         hybrid     14
                                                                              hybrid      

P-111        2011-FA      01              12:30 PM       day         26

                  2011-FA                        11:30 AM       day

P-241        2012-SU     S-101                                other       23

Notice the count doubles in my crosstab where there exists multiple start times. I only wante to count once. 

                   Day             Hbrid       Other            Total

P-099                            28                                  28  

P-111         52                                                     52

P-241                                              23                23

TIA,

[Former Member · Accepted solution]

Hi Dee,

Try adding the three objects to the cross tab to the columns [Section ID];[Term];[Meeting Start Time] and use Hide dimension option to hide them, so that the variable Var2 works the same way as in vertical table as the cross tab would be at same grain then.

Thanks

Mallik

[Former Member · Accepted solution]

Thanks, Mallik,

I tried your suggestion above: just adding the [Meeting Start Time] to my cross tab column at least yields the correct count & doesn’t double count (attached screenshot, # 1) But, my goal is to get my crosstab to display similar to screenshot #2.  So, my questions is: 

1. How can I display the count for the four courses (PTA 099, 111, 212, 213) only once?  These are
   courses either with multiple instructors and/or two instructionalmethods (lecture & lab) .  I thought my Var2 (Min function) will take care of this. 
   
   
2. Once I get my crosstab to display the correct count, what is best way to hide the object s.   Make the value the same color as the background and then make the column very small (narrow), or is there a better way.  I’m using XI 3.1

Regards,

[Former Member · Accepted solution]

Hi Dee,

Apply break on Type and the use the Var2 in break footer,so that count shows up for each Type in break footers, Add Type also to the break footer.

After that use fold/unfold or outline feature on type and hide the details to show only summary which would be exactly same as the data in screenshot #2.

Thanks

Malllik

[Former Member · Accepted solution]

Hi Malllik,
Thanks for the reply.  I tried what you suggested above, and I’ve attached the result.  I think we
are pretty close…

 

Regards,