1 Reply Latest reply on Dec 6, 2016 5:39 PM by Shinichiro Murakami

    Showing measure in only specific years

    Derek Wong



      I'm trying to only show a measure for specific years, is it possible to use exclusions or otherwise to do this? I'm aware of "hiding" individual values, but would much prefer that the heading wouldn't even appear for the irrelevant years (i.e. The row isn't just blank for the year we want to exclude, but instead that the entire row doesn't exist)


      I've attached a workbook that has the general form of the crosstab I'm working with. The intended result would be if we could get, say, Profit Ratio to only show in 2015, and 2012-2014 only returning two rows, Profit and Sales.



      Let me know if I can clarify what I'm saying, thanks!


        • 1. Re: Showing measure in only specific years
          Shinichiro Murakami



          It's not possible in this data structure.


          If you are OK to change data with using Union (or Pivot), you can get expected result.

          But remember this solution is crazily troublesome.

          Don't recommend at all.

          I just tried I could do that or not.


          The whole difficulty is coming from that you try to filter "Measure value" itself from specific dimension.

          You cannot do that in straight forward way.

          So need to re-create "Value" to combine whole three measures together , then divide them again.





          if attr([Table Name])="Orders" then sum([Profit])

          elseif attr([Table Name])="Orders$" then sum([Profit]) / sum([Sales])

          elseif attr([Table Name]) ="Orders1" then sum([Sales])



          [Value Category]

          if attr([Table Name])="Orders" then "Profit"

          elseif attr([Table Name])="Orders$" then "Profit Ratio"

          elseif attr([Table Name]) ="Orders1" then "Sales"



          Below two are for displaying % as percentage and others as whole number.


          [Value (Whole)]

          if abs([Value])>1 then [Value] end


          [Value( percentage)]

          if abs([Value])<= 1 then [Value] end



          [Year+ Value Category]

          attr(str(year([Order Date])))+[Value Category]