Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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. 

4 Replies
Not applicable

Re: Exclude Dimension from Expression results

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

Let me know.

sebastiandperei
Valued Contributor

Re: Exclude Dimension from Expression results

Hi!

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

Not applicable

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.

sebastiandperei
Valued Contributor

Re: Exclude Dimension from Expression results

We need to know what the variables contain...

Community Browser