3 Replies Latest reply on Jul 8, 2016 5:34 PM by swaroop.gantela

    Inter-rows calculated field

    Silvia Calo

      Hi there!

       

      I have a dashboard which has some data already aggregated and organized as the table below:

       

           

      SvyIDIndpVarsVariableAdminMean
      12/1/2015LiberiaImpRiceCupNULL27.06165
      12/1/2015LiberiaManLbrNULL234.7983
      12/1/2015Aggregation2ImpRiceCupBong26.2
      12/1/2015Aggregation2ImpRiceCupGrand Bassa24.85
      12/1/2015Aggregation2ImpRiceCupLofa28.46667
      12/1/2015Aggregation2ManLbrBong196.1
      12/1/2015Aggregation2ManLbrGrand Bassa234.95
      12/1/2015Aggregation2ManLbrLofa217.2
      2/1/2016LiberiaImpRiceCupNULL27.12275
      2/1/2016LiberiaManLbrNULL242.3787
      2/1/2016Aggregation2ImpRiceCupBong26.5
      2/1/2016Aggregation2ImpRiceCupGrand Bassa24.95
      2/1/2016Aggregation2ImpRiceCupLofa29.23333
      2/1/2016Aggregation2ManLbrBong199.2
      2/1/2016Aggregation2ManLbrGrand Bassa239.6
      2/1/2016Aggregation2ManLbrLofa226.75

       

       

      as you can see in the workbook attached, I would like the map to be a trigger for the graph. The graph has to contain a line for the mean variable in the selected region and another line for the mean of that same variable for all country.

      As of now, all this works on my workbook because the overall mean is a field calculated using this formula:

       

      { FIXED  [Variable],[Indp Vars],[Svy Date (Months)]: AVG([Mean]) }

       

      the problem is that I would like to instead of getting the mean as the mean of the means in each region, to use the values I have under IndpVars='Liberia', as in the table below;

       

            

      SvyIDIndpVarsVariableAdminMeanoverall Mean
      12/1/2015LiberiaImpRiceCupNULL27.06165
      12/1/2015LiberiaManLbrNULL234.7983
      12/1/2015Aggregation2ImpRiceCupBong26.227.06165
      12/1/2015Aggregation2ImpRiceCupGrand Bassa24.8527.06165
      12/1/2015Aggregation2ImpRiceCupLofa28.4666727.06165
      12/1/2015Aggregation2ManLbrBong196.1234.79831
      12/1/2015Aggregation2ManLbrGrand Bassa234.95234.79831
      12/1/2015Aggregation2ManLbrLofa217.2234.79831
      2/1/2016LiberiaImpRiceCupNULL27.12275
      2/1/2016LiberiaManLbrNULL242.3787
      2/1/2016Aggregation2ImpRiceCupBong26.527.12275
      2/1/2016Aggregation2ImpRiceCupGrand Bassa24.9527.12275
      2/1/2016Aggregation2ImpRiceCupLofa29.2333327.12275
      2/1/2016Aggregation2ManLbrBong199.2242.37866
      2/1/2016Aggregation2ManLbrGrand Bassa239.6242.37866
      2/1/2016Aggregation2ManLbrLofa226.75242.37866

       

      so is there a way that I can replace something like Mean[IndpVars='Liberia'][Variable='ManLbr]) to AVG([Mean]) in the formula above? or is there another syntax to calculate the overall Mean using the data already calculated that I have?

       

      this is the only solution that I see to get the map to interact with the chart so that if I select one region I see both lines and if I don't select anything I get the overall mean line only, but any other suggestion to get the same even without the calculated field is welcomed!

       

      Many thanks in advance.

      Silvia

        • 1. Re: Inter-rows calculated field
          swaroop.gantela

          Silvia,

           

          I apologize if I did not catch all of the issues involved,

          but it seemed like the syntax you are looking for may be:

          { FIXED  [Svy Date (Months)]: AVG(IF [Indp Vars]="Liberia" AND [Variable]="ManLbr" THEN [Mean] END) }

          • 2. Re: Inter-rows calculated field
            Silvia Calo

            Hi,

            many thanks for your suggestion! starting from there, I came out with the syntax that I needed, which is:

             

            { FIXED  [Svy Date (Months)],[Variable]: AVG(IF [Indp Vars]="Liberia"  THEN [Mean] END) }

             

            so I got the data organized the way I thought could solve my problem, but it actually didn't solve it.. in fact, now when I don't select anything, the overall mean line shows up well, but for the regional one I get all the values together, while I don't want it to show up either... I guess that the only solution here is to somehow overlay a graph with both lines to another graph with the overall mean only so that when I select a region I get the right graph, while when I don't select anything I get an empty graph (by choosing exclude all values in the action) and can overlay the chart with the overall mean only..

            so now the question becomes: how can I overlay 2 sheets in a dashboard without setting them to be floating? any suggestion on how to do that?

             

            many thanks!

            Silvia

            • 3. Re: Inter-rows calculated field
              swaroop.gantela

              Silvia,

               

              Please see if the attached might work for you.

              When the map is cleared, then all the regions are showing,

              so you can try and use to color that condition white:

              MIN(Mean)=MAX([Mean])

               

              When only on region is selected, there is only one value so

              the min will equal the max, but if there multiple regions (as in the

              case when a region is unclicked), the min will not equal the max.

               

              So I think you can put that boolean on the Color shelf and then

              for the False case, change the color to white (please see image below).

               

              Of note, this will only work if the user selects one region at a time.

              If one ctrl+click on multiple regions, then the combination will turn white.

              You can force the user to only select one by editing the action and checking

              the "Run on single select only" box.

               

              210512white.png

              210512map.png