1 Reply Latest reply on Jun 13, 2016 12:46 AM by Erik Klemusch

    How to calculate differences when using parameter-based measures and dimensions

    Erik Klemusch

      Hello forum members,

       

      I´m struggling to find a solution to a parameter related problem that doesn´t kill the performance of my entire worksheet. I would be glad if somebody could help with this!

       

      Here is what I´m trying to do:

      There is one parameters that allows users to either pick a preconfigured date range (e.g. "last week") or to choose his own date range.

      Based on the user´s selection, the field "Date Range Qualifier" will qualify all dates within the data set as either within "Selected Date Range", as within "Comparison Date Range" or as NULL.

       

      Then there is a second parameter called "Rank Defining Metric". This one allows users to pick a certain measure. All tables within the dashboard will be sorted with respect to this measure.

       

      Now here is the critical point: I would like to calculate the difference between "Selected Date Range" and "Comparison Date Range" regarding the chosen rank defining metric.

      I´m sure that there is an easy, Tableau-style way to do this, but I just can´t find it.

      The only thing I´ve come up with was to create a new measure for each of the Date Ranges and to calculate the difference between them.

      The calculation for the Selected Date Range e.g. looks like this:

       

             { INCLUDE [Date Range Qualifier]:

                IF COUNT(IF [Date Range Qualifier] = 'Selected Date Range' THEN 1 END) >= 1 THEN

                [Chosen Rank Defining Metric]

                ELSE 0.00

                END}

       

      I´ve attached an example dashboard for this problem. If somebody has a good idea on how to get to the desired result more easily, please tell me!

       

      Thanks for any help!