0 Replies Latest reply on Aug 18, 2016 12:46 PM by Vincent Baumel

    LODs set to a Parameter

    Vincent Baumel

      Towards the end of yesterday's TUG meeting, I brought up a question about setting the conditions of an LOD expression to a parameter choice. I found a simple way to achieve this, and I wanted to share it with everyone.  Using the Superstore dataset, I wanted to allow the user to set the sales aggregation level based on location. There's a hierarchy consisting of Country-State-City-Postal Code, so my solution focuses on these. I started by creating an LOD calculation for each dimension, that all followed the same pattern:


      {FIXED [location dimension] : SUM(SALES) }


      Once I had all of these in place, I created a string parameter called "Location Level" with options matching the location choices. Finally I created a calculation called LocationSales, that reads as follows:


      IF [Location Level] = 'Country' THEN [zCountry Sales]

          ELSEIF [Location Level] = 'State' THEN [zState Sales]

          ELSEIF [Location Level] = 'City' THEN [zCity Sales]

          ELSE [zPostal Sales]



      As you can see in the attached workbook, the sales shown in the DynamicSales worksheet match the aggregation level as if it had been entered in the LOD expression literally. I colored the top worksheets based on the level of aggregation, so that you can see where the LOD draws the boundaries of the calculation (for example, CitySales will have the same amount shown for each Postal Code within a city).  If anyone is unclear on how FIXED level of detail calculations work, this is a pretty handy way of learning about it. Hope it helps!