2 Replies Latest reply on Nov 25, 2012 7:05 AM by Alec Guerenstein

    Question on Table Calculations

    Alec Guerenstein

      I am having some issues solving the following problem:

      - Single measure, I need to calculate a single value within a time-frame (V = StdDevp(t1,t2) / Avg(t1,t2); where t1, t2 is the interval)

      - In my Viz I need to show the fluctuating measure throughout time as a line, and the single value of V as a horizontal line between t1 and t2 (which is a sub-space of the overall time depicted for the measure).

       

      I believe I have a problem with the order in which the table calculations are resolved, making my "V" a value that varies along the time axis.

      I would have to "freeze" the value of V (calculate it first) to draw if afterwards...

       

      Any ideas / suggestions?

       

      PS: I tried duplicating the connection to use Window functions (avg and stdevp) applied to the sub-space t1, t2, but couldn't make it work.

      I am trying to avoid RAWSQL but I am getting thin of options....

       

      Thanks!

      Alec

        • 1. Re: Question on Table Calculations
          Jonathan Drummey

          Hi Alec,

           

          Could you post a packaged workbook (.twbx) with some sample data, and maybe even a mockup of the desired graph? What you describe isn't sufficient for me to identify where the problem is and what the solution might be.

           

          Jonathan

          • 2. Re: Question on Table Calculations
            Alec Guerenstein

            Hi Jonathan,

            Attached the "mockup" design in an Excel file.

            As you'll see, Sales is the single measure varying throughout time, and "V" is calculated according to the formula mentioned.

            Since the same "V" is based in the time-frame specified, it should be plotted as a horizontal line for the specific time (as a dual-axis, since it's a ratio) .

             

            I am in the middle of the following:

            - Duplicating the connection used for "Sales", with no blending or join (to prevent the "rolling effect" I have when using Window_Avg/StdevP)

            - Using Parameters to catch Week_From and Week_To (to use them in a Dashboard for both connections)

            - Calculating "V" on the secondary measure for the whole window, which is filtered to Week_From/To and hence should remain constant and accurate

             

            Any comments or suggestions are more than Welcome. Thanks for helping out!

            Alec.