8 Replies Latest reply: Jan 24, 2017 6:15 AM by Johann Bauer RSS

    Aggr with set Analysis

    Johann Bauer

      Hi folks,

      looks like the problem with set analysis and aggr is not so uncommon.

      I allready found some very interesting blogs from henric about this topic but wasnt able to solve this.

      First of all i am using a drill down dimension but im not able to reference to this dimension in the expression for a visualization.

      So I build a small workaround to get me the coloumn name of the current dimension and store this in a variable.

       

      Now to my real problem.

      I'am using this expression for my charts.

      Sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI3)','$(vKPI2)','$(vKPI1)'}>}Betrag)

       

      This expression works and gives me the sum over all mentioned restrictions. Now I want the min and max values grouped by my

      drill down dimension. I read somewhere that the aggr function works like a  group by in the load script.

      First i tried following expression:

      max(aggr(sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Betrag),$(vOrganisationHierarchie)))

       

      $(vOrganisationHierarchie) delivers the current column name of the drill down dimension


      But the expression wont deliver any values:


      My second try was to try the aggr function in the set analysis expression like this:


      sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Aggr(Betrag),$(vOrganisationHierarchie))


      and the second try also didnt deliver any values.


      tldr:

      I need the min and max values of an set analysis grouped by a drill down dimension.


      Hope somebody can help me with that.



      KR

      Johann