[Mondrian] average percentage values with visual totals/totals

[Ati Rosselet]

Julian,
I tried to take your suggestion by doing this:
  member [Account].[LSYH].[count] as 'count({(CurrentDateMember([Time],
"[""Time""]\.[yyyy]").FirstChild : CurrentDateMember([Time],
"[""Time""]\.[yyyy]\.[P-yyyy-m]").Lag(1))}))', FORMAT_STRING = "#.00"
and
  member [Account].[LSYH].[Relative %] as '[Account].[LSYH].[Project %]
/[Account].[LSYH].[count]',FORMAT_STRING = "0.0%"

I managed to get a value that presents correctly in the visual total sum
column, but the individual column values are not correct.. I'm stuck here.
Maybe what I'm trying to do is conceptually not possible...

basically the entire MDX is :
with set [VisualTime] as '{VisualTotals({CurrentDateMember([Time],
"[""Time""]\.[yyyy]"), (CurrentDateMember([Time],
"[""Time""]\.[yyyy]").FirstChild : CurrentDateMember([Time],
"[""Time""]\.[yyyy]\.[P-yyyy-m]").Lag(1))})}'
  member [Account].[LSYH].[Direct result] as '([Account].[LSYH].[Revenues]
+ [Account].[LSYH].[PROJECT])'
  member [Account].[LSYH].[Project charging] as '[Account].[LSYH].[PROJECT]'
  member [Account].[LSYH].[hereof: Test] as '[Account].[LSYH].[Cost from
test]'
  member [Account].[LSYH].[hereof: other] as '([Account].[LSYH].[Cost from
others] + [Account].[LSYH].[Cost to others])'
  member [Account].[LSYH].[count] as '1/count({(CurrentDateMember([Time],
"[""Time""]\.[yyyy]").FirstChild : CurrentDateMember([Time],
"[""Time""]\.[yyyy]\.[P-yyyy-m]").Lag(1))}))', FORMAT_STRING = "#.00"
  member [Account].[LSYH].[Relative %] as '[Account].[LSYH].[Project %]
*[Account].[LSYH].[count]',FORMAT_STRING = "0.0%"
member [Account].[LSYH].[Project %] as '([Account].[LSYH].[Direct result] /
[Account].[LSYH].[Revenues])', FORMAT_STRING = "0.0%"
select Crossjoin({[VisualTime]}, {[Invoice Type].[Actual data], [Invoice
Type].[Budget]}) ON COLUMNS,
  Crossjoin({[Project].[PROD.EAI], [Project].[PROD.IOCC CoCo],
[Project].[PROD.Netline Market ], [Project].[PROD.Netline Plan ],
[Project].[PROD.Netline/Load], [Project].[PROD.Netline/Sched ],
[Project].[PROD.Sched Connect ], [Project].[PROD.Service Mgmt],
[Project].[PROD.WAB]}, {[Account].[LSYH].[Revenues],
[Account].[LSYH].[Direct result], [Account].[LSYH].[hereof: other],
[Account].[LSYH].[hereof: Test], [Account].[LSYH].[Project %],
[Account].[LSYH].[Relative %], [Account].[LSYH].[Project charging]}) ON ROWS
from [FI]
where {[Measures].[amount_EUR]}


and ideally I would like to have the [Project %] values in the columns, and
in the VisualTotal column, the value from [Relative %] or the sum of the
values/number of values.

Cheers and thanks for your help!


On Mon, May 13, 2013 at 8:21 PM, Julian Hyde <jhyde at pentaho.com> wrote:

> Maybe you could calculate a 'count' and 'sum' member using VisualTotals,
> and then divide them.
>
> Julian
>
>
> On May 13, 2013, at 4:57 AM, Ati Rosselet <ati.rosselet at gmail.com> wrote:
>
> oops... sent too soon.. sorry.
> No..  here we calculate inline as part of the "with" statement. e.g.:
>
> member [Account].[LSYH].[Project %] as '([Account].[LSYH].[Direct result]
> / [Account].[LSYH].[Revenues])', FORMAT_STRING = "0.0%"
>
> other values in the rows are plain numbers, so their summing into VT is
> perfectly ok.
> Any Ideas?
>
>
> On Mon, May 13, 2013 at 1:52 PM, Paul Stoellberger <
> p.stoellberger at gmail.com> wrote:
>
>> Is the measure you are talking about an AVG aggregator value? Meaning the
>> percentages are stored as numbers in the fact table and then you create an
>> AVG measure on top of that?
>> If so VisualTotals() should calculate it fine, in the end its just
>> calling Aggregate() on the children
>>
>> If not, schema / MDX would help
>>
>> -Paul
>>
>> On May 13, 2013, at 1:28 PM, Ati Rosselet <ati.rosselet at gmail.com> wrote:
>>
>> > Hi,
>> > We are using visual totals in out mdx, but have an element which is a
>> percentage value, and which we would like to show as avg  of the visible
>> percentages as opposed to sum (I can't offhand think of a situation where
>> one would want to SUM percentages... ).
>> >
>> > Is there any way of getting visual totals to avg certain elements as
>> opposed to summing them?  Or any suggestion as to how this could be set up?
>> > Thanks
>> > Ati
>> > _______________________________________________
>> > Mondrian mailing list
>> > Mondrian at pentaho.org
>> > http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130516/c90e4072/attachment.html