how to get one expression horizontal and one vertical in pivot table?

[stevelord]

Hi, I have a pivot table where a dimension 'Event' is pivoted horizontal and sum(points) is the expression used to show the point values of the Events completed.

User     Event     Run     Walk     Swim

Joe                    10          5          15

John                    10          -          -

Jane                    -          5          15

I want to add another expression to count the events, and which updates itself if I narrow or expand the particular events to be displayed,

So it might look like this if I have the same events selected as above:

User     EventCount     Event    Run     Walk     Swim

Joe          3                              10          5          15

John          1                              10          -          -

Jane          2                              -          5          15

But go to this if I select only Run and Walk from the Event listbox:

User     EventCount     Event    Run     Walk

Joe          2                              10          5

John          1                              10          -

Jane          1                              -          5

Approaches tried and failed:

1. Trying in script, but that keeps the EventCount to the total they have in the source data and ignores selections in the listbox (as expected really).

2. Adding expression in the pivot table causes the EventCount values to go in as second columns beside the events and gives everything a 1.

3. Adding calculated dimension in the pivot table causes an //error in expression message or something like that, even though the expression is okay and simply Count(Distinct Event).

3a. I tried a variety of calculated dimension expressions.  closest to success was along the lines of count(Total Points), and looked right when only one person was selected, but flipped to the total count for the entire table when many people were selected.  I tried building in aggr(Points, User) into it to split that out to no avail.

4.Totals-> Number Count.  This appears on straight table, but disappears on the pivot table and probably not what I need.

Any help would be appreciated.  I feel like I'm getting close on the calculated dimensions.  Also, I can't just make the Events into new fields themselves because in reality there are hundreds and the lists vary by client.  It would be a pretty big pain to add new events to the table every time they are created.

Thanks again!

[its_anandrjs]

Try with

=Count( If( YourExpression > 0 , 1 ) )  //For  EventCount

[stevelord]

PS> As a workaround, I can have a little straight table like this:

User     EventCount

Joe     3

John     1

Jane     2

But we want all this stuff together in one table so as to minimize vlookups.  (Every time I find myself doing a vlookup, the next thing I do is go drop in whatever that field was or join another thing to a thing.  We'd be swimming in vlookups pretty fast if we let them accumulate. )

[stevelord]

Thanks, this put me in the right track to persist with counting in the calculated dimension, but still some trial and error was needed. This expression appears to be doing the trick and not counting nulls.

=RangeSum(Aggr(TextCount(Distinct Event),User))

That said, it seems to be pretty demanding on the server.  If I have to choose between vlookup or crash server, I'll go with the vlookup. Will see how it flies for now.    I'm qa'ing against a straight table of the same data now to be sure, but it looks good on spot checking whether I select many people or few, many events or few.

[stevelord · Accepted solution]

Fyi, I switched between numcounts on the points and textcounts on the event names.  Some events are worth 0, but people have credit if they have a 0 instead of a null.  Aggr() was important to confine the counts by userid, and rangesum() and rangecount() seem to be the only types of expressions to work in a calculated condition around another expression (Kind of the way I had to use those to make a pie chart work right or something awhile back).

Confirmed that this expression is working, and I think I saw people trying to hammer out something similar elsewhere without getting to an answer, so will tag this as the correct answer for visibility.

=RangeSum(Aggr(TextCount(Distinct Event),User))

[raajeshn]

Hi Steve,

Just wanted to check if you had the same look and feel as in your example, if so - can you please share your QVW? Thanks.

Thanks & Regards,

Raajesh N