Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Collapse more rows to get unique average value

  1. #1
    Join Date
    Dec 2012
    Posts
    121

    Default Collapse more rows to get unique average value

    Hi all, i built a saiku query to view my results, but this is not the output i would like to see.
    Referring to the image on the attachment, i'd like to have for each day the average value of the rows which belongs to it.
    I explain better, using the image values.
    I would like to obtain the following output:

    Numero Test
    day TIMVodafoneWind 3Italia

    20130501 0 0 6.5 6.5
    20130502 10.25 0 0 3
    20130503 3.6 0 7.6 3.6
    20130504 0 0 17.5 0.5

    Name:  img.png
Views: 29
Size:  12.1 KB

    Any ideas?
    Yamas

    Marco

  2. #2
    Join Date
    Dec 2012
    Posts
    121

    Default

    I forgot the query:

    with
    set [filter_date] as 'IIf(("DAILY" MATCHES "YEARLY"), {(Ancestor([Tempo.default].[2013], [Tempo.default].[year]) : Ancestor([Tempo.default].[2013], [Tempo.default].[year]))}, IIf(("DAILY" MATCHES "MONTHLY"), {(Ancestor([Tempo.default].[2013].[201305], [Tempo.default].[month]) : Ancestor([Tempo.default].[2013].[201305], [Tempo.default].[month]))}, IIf(("DAILY" MATCHES "WEEKLY"), {([Tempo.YW].[2013].[201318] : [Tempo.YW].[2013].[201318])}, {(Ancestor([Tempo.default].[2013].[201305].[20130501], [Tempo.default].[day]) : Ancestor([Tempo.default].[2013].[201305].[20130504], [Tempo.default].[day]))})))'

    set
    [lista_bin] as

    nonemptycrossjoin(
    [filter_date],
    Descendants([Bin.default].[CENTRO].[LAZIO].[ROMA].[ROMA], [Bin.default].[250m])
    )

    set [filter_bin] as Filter(
    [lista_bin], Sum([Measures].[Numero Test]) > 10)


    SELECT
    [filter_bin] on rows,
    CrossJoin([Measures].[Numero Test], [Operatore].[operatore].AllMembers) on columns

    FROM [Cubo]
    where ([TipologiaLocalizzazione].[1])



Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.