3 Replies Latest reply: Sep 5, 2012 10:02 AM by Aaron Couron RSS

    Calcualted Dimension Question

      I'd like to create a straight table with an undefined dimension.  They are actual 3 distinct (and sometimes overlapping) sets...

       

      <Fiscal Year = {$(vMaxFY -1)}>    ex. 2011

       

      <[Fiscal Year]={$(=vMaxYear-1)}

                  ,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} >  ex. 2011 YTD

       

      and

       

      <Fiscal Year = {$(vMaxFY)}>   ex. 2012 YTD

       

      I have the following expressions.

      Sum([Total Cost]) , Count([SSN]) , Avg([Total Cost])

       

       

      I'm currently doing this with 9 text boxes, but I feel like a straight table would be a better solution.

       

      Can someone provide some guidance???

       

      Thanks and best regards,

      Keith

        • Re: Calcualted Dimension Question
          Aaron Couron

          Maybe someone can correct me, but I am not sure this is possible in one straight table because your dimension rows (in this case your time periods) are overlapping.  I would create 3 tables of one row each, run horizontally, still "dimensionless" with each of your three set types.  I would do the 2011 one with a header row so you can label the expressions and then suppress the headers on the other two so I could align them up close to the first table so they appear as one table.

          • Re: Calcualted Dimension Question

            I'm adding a picture of what is being displayed right now...

             

            Q1 Data Table.png

              • Re: Calcualted Dimension Question
                Aaron Couron

                What you have done by putting together all these text boxes is not bad and probably now that it is done, leave it alone???  But it will be a hassle when you want to move the beast around so it might be worth the work to get it into three tables that look as one rather than 9+ text boxes that look as one.  Regardless, neither solution would give you one table.  I drew this up in MS Excel to show you what I would do:

                 

                Table 1 (with Headers)

                 

                Total Cost (Millions)Volume of EncountersAverage Cost
                2011SUM({<Fiscal Year = {$(vMaxFY -1)}>} [Total Cost])COUNT({<Fiscal Year = {$(vMaxFY -1)}>} SSN)AVG({<Fiscal Year = {$(vMaxFY -1)}>} [Total Cost])

                 

                 

                Table 2

                 

                2011 thru FP10SUM({<[Fiscal Year]={$(=vMaxYear-1)}
                ,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } [Total Cost])
                COUNT({<[Fiscal Year]={$(=vMaxYear-1)}
                ,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } SSN)
                AVG({<[Fiscal Year]={$(=vMaxYear-1)}
                ,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } [Total Cost])

                 

                Table 3

                2012 thru FP10SUM({<Fiscal Year = {$(vMaxFY)}>} [Total Cost])COUNT({<Fiscal Year = {$(vMaxFY)}>} SSN)AVG({<Fiscal Year = {$(vMaxFY)}>} [Total Cost])

                 

                Hope this helps.