4 Replies Latest reply: Aug 23, 2012 5:50 PM by Sebastian Pereira RSS

    Exclude Dimension from Expression results

      Hello,

       

      I'm wondering if there is a solution for the below issue before I create a workaround.

       

      I need to be able to return the result of an expression without it being split by the dimension shown in the chart. 

       

      I have a weekly goal that is calculated by Week on a weekly chart, but I when I use the same calc on the Person chart, it splits all the data points by the Person Dimension and ends up being waaay off due to how the person relates in the data model. 

       

      I would like to be able to either reference the results of one calculation in a different chart as a static number (without keying it in every week manually), or be able to exclude the dimension from this particular expression so that it gives me my weekly goal per user.

       

      Weekly Goals:

       

      DispCal.WeekEndDt     Goal
      8/24/201269
      8/17/201299
      8/10/201291
      8/3/201259

       

      John (and all other users) should have 69 for 8/24 instead of 6, but when the dim.UserName is included QV takes that and splits all the data with whatever John is related to and thus, the goal calc becomes useless.

       

      Goals per user:

       

      dimUsers.NameDispCal.WeekEndDt    Goal
      John8/24/20126
      John8/17/201210
      John8/10/20129
      John8/3/20126

       

      When it is calculated by user, QV limits the data and throws the calc off.

       

       

       

      Thanks, and please let me know if I need to clarify. 

        • Re: Exclude Dimension from Expression results

          If I understand your requirement, this is what you are looking for.

          Let me know.

          • Re: Exclude Dimension from Expression results
            Sebastian Pereira

            Hi!

             

            If you are using sum(Goal), try with sum(Total <DispCal.WeekEndDt> Goal)

            • Re: Exclude Dimension from Expression results

              Thanks for the replys, but I dont think I'm able to apply them in this situation.

               

              qlikdisqruser1, that would work if the goals were already calculated and in a table, prob is as of right now, I used QV to calculate the goals.  Thanks, though.

               

              Sebastian, I'm unfamiliar with the Total function, it might work, but I'm not sure how to apply it.  Can you break down what it does?

               

              I think my problem is that the goal is not in a table, it is a calulation of a few variables defined in the script.  When you put in the person dimension, QV automatically assumes you want the calc split out by the individuals, while with this situation, I dont want it to do that.

               

              Not that it matters, but here is the Goal calc:

               

              ((($(1stGoal)*$(eSum_e_1stOtherGoal)))/$(PersonCount))+(($(2ndGoal))*($(eSum_e_2ndGoal)))/$(PersonCount)+(($(3rdGoal))*($(eSum_e_3rdGoal)))/$(PersonCount)

               

              Basically, A+B+C/Count of users.

               

              That calc comes out fine for weekly values because the records are not assigned to a user and are easily aggregated.  

               

              Think call center where many users are calling many leads and there is overlap.

               

              When I add the person dimension in, due to the realational data model, QV looks for the User in the datamodel and assigns only records that user is associated with, and returns a significantly lower value for the same/simular calc, as the user has only dealt with a small sample of records. 

               

              Is there a way through set analysis to disregard the dimension, or reference a result from another chart?

               

              My next option is to write some sql to do the goal/user/week calc and bring it in as a field in the data model and be done with this madness. 

               

              Thanks again, please let me know if there are any other ideas/options.