Author Topic: Dynamically select measures  (Read 11317 times)

Offline nayitian

  • Senior Member
  • ****
  • Join Date: Jan 2007
  • Posts: 52
  • Forum Citizenship: +1/-0
Dynamically select measures
« on: 08 Mar 2010 10:29:57 am »
Hello all,

I have the following information

ProductionLine          Metric           Value
   PL1                      M1                 1
   PL1                      M2                 2
   PL1                      M3                 3
   PL2                      M1                 6
   PL2                      M2                 5
   PL2                      M3                 4
From the information, I shall report the metric with the maximum values of each ProductLine, which is supposed to be like this
   PL1      M3    3
   PL2      M1    6

I am stucked in how to dynamically select the metric. Could anyone give some help on this. Thank you very much.


Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,475
  • Forum Citizenship: +633/-10
  • Cognos Software Muppet
Re: Dynamically select measures
« Reply #1 on: 09 Mar 2010 05:32:00 am »
Hi,

Group on ProductionLine.  Add a Query Calculation called Val with the expression total([Value] for [Metric]).  Add a second Query Calculation called Max with the expression maximum([Value] for [ProductionLine]).  Add a filter with the expression [Val] = [Max] and set the timing to 'After auto aggregation'

MF.
Meep!

Offline nayitian

  • Senior Member
  • ****
  • Join Date: Jan 2007
  • Posts: 52
  • Forum Citizenship: +1/-0
Re: Dynamically select measures
« Reply #2 on: 11 Mar 2010 08:14:54 am »
Thank you very much MF. It worked perfectly. Instead of using maximum, I used rank to pick the top n, it accomodates users' need better.

However, I need further help if I could. How about this time, the table is a crasstab like this

                      REGION1     REGION2   REGION3   REGION4  REGION5  Total(REGION)    ranking??
PL1   Metric1       1               1             1             1              1           5
        Metric2       2               2             2             2              2           10
        Metric3       3               3             3             3              3           15
PL2   Metric1        3               3             3             3              3           15 
       Metric2         2               2             2             2              2           10
       Metric3         1               1             1             1              1           5

Can I still add an item to rank by Total(REGION), then pick the top 1 or 2 ?
Thank you.

 


       
Twittear