Calculating a new column in crosstab with 2nd order solving

[ninjamayo]

I have a complicated crosstab that essentially has a number of columns that I am reporting on. The last column brings back a variable number of entries and under that column I have another calculated column like in my example below:

                           FIRST COUNT | SECOND COUNT |                   TYPE ID
SEGMENT                                                                   TYPE COUNT | PERCENT COUNT

I can easily calculate TYPE COUNT because its a simple calculation however my PERCENT COUNT formula needs the COUNT for the specific TYPE ID / OVERALL COUNT for that segment. The OVERALL COUNT I get in my calculations is always the one for the TYPE ID so I cannot get the overall one. I tried using different solving orders but that didnt work. Any ideas how to solve this?

[MFGF]

I have a complicated crosstab that essentially has a number of columns that I am reporting on. The last column brings back a variable number of entries and under that column I have another calculated column like in my example below:

                           FIRST COUNT | SECOND COUNT |                   TYPE ID
SEGMENT                                                                   TYPE COUNT | PERCENT COUNT

I can easily calculate TYPE COUNT because its a simple calculation however my PERCENT COUNT formula needs the COUNT for the specific TYPE ID / OVERALL COUNT for that segment. The OVERALL COUNT I get in my calculations is always the one for the TYPE ID so I cannot get the overall one. I tried using different solving orders but that didnt work. Any ideas how to solve this?

Dimensional or relational package?

MF.

[ninjamayo]

Dimensional (I think) :)

[MFGF]

Dimensional (I think) :)

In that case, getting the overall count is probably going to involve using the completeTuple() function. Are the counts measures, or are they calculated?

MF.

[ninjamayo]

The counts are calculated on other crosstab columns.

[MFGF]

The counts are calculated on other crosstab columns.

Can you share how?

MF.

[ninjamayo]

Yes, definitely. Sorry I didnt make that clear.

So for example FIRST COUNT is Count(FIRST) for that particular SEGMENT. So under my TYPE ID my PERCENT COUNT is TYPE COUNT / FIRST COUNT for that particular SEGMENT.

I tried the completeTuple() but I get the count for all the segments. So nearly there with your help :).

Thanks so much.

[MFGF]

Yes, definitely. Sorry I didnt make that clear.

So for example FIRST COUNT is Count(FIRST) for that particular SEGMENT. So under my TYPE ID my PERCENT COUNT is TYPE COUNT / FIRST COUNT for that particular SEGMENT.

I tried the completeTuple() but I get the count for all the segments. So nearly there with your help :).

Thanks so much.

Sorry - can you be a little more specific about the syntax? What exact syntax are you using for your count? What expression did you use in your completeTuple() calculation? Have you referenced currentMember([the hierarchy your Segment members belong to]) within the completeTuple?

MF.

[ninjamayo]

completeTuple([FIRST]) but I dont know how to use the currentMember. I 've been trying to do completeTuple([FIRST], [SEGMENT]) but it doesn't seem to work.

Any ideas?

[ninjamayo]

Aha, I think I got it.

I used Total([FIRST] FOR [SEGMENT]) and that seemed to do the job.

Thanks anyway!

[MFGF]

completeTuple([FIRST]) but I dont know how to use the currentMember. I 've been trying to do completeTuple([FIRST], [SEGMENT]) but it doesn't seem to work.

Any ideas?

Yep. [SEGMENT] is a set not a member, so you can't use it as the argument of a tuple. Your set of [SEGMENT] members belong to a hierarchy within your package. You can isolate the current member being used using the currentMember() function, so you get

completeTuple(currentMember([your hierarchy]), [FIRST])

This is the way to do things with a dimensional source.

Aha, I think I got it.

I used Total([FIRST] FOR [SEGMENT]) and that seemed to do the job.

Thanks anyway!

Ouch!! No - don't do that! total([something] FOR [something else]) is a relational summary, not a dimensional summary. It's not the way to do it with a dimensional package.

MF.

[ninjamayo]

Hi,

just saw your comment. So why Total([FIRST] FOR [SEGMENT]) is not a good idea? It seems to work at the moment so is there a problem in terms of performance?