Averaging value of column and appying visual cues

[#1]

Hi There,

I am hoping one of you bright sparks can help me with a problem (I have attached an example file).

Basically what I have is a list of names, account ID's and then a target for each account,

In my table I am displaying a count of distinct account ID's where there is a sum target greater than 1, this limits my data down to 3 names from the 4 as some don't have targets.

What I would now like to do is work out the average number of accounts for just those displaysing (so on the 7 rather than all 10 accounts), so in the example table 7/3 and then take that value and if the number of accounts falls 20% above or below that average value, change the background colour to flag this up

Many thanks for the help

[#2]

I'm not sure exactly what you are going for. First, you can remove the if and just use Set Analysis for the first expression:

Count ({<Target = {'>1'}>} DISTINCT [Account ID])

Then to get the total for all of the names (your 7):

Count ({<Target = {'>1'}>} DISTINCT TOTAL [Account ID])

Your percentage for that would be something like:

Count ({<Target = {'>1'}>} DISTINCT [Account ID])
/
Count ({<Target = {'>1'}>} DISTINCT TOTAL [Account ID])

I'm not sure what you want to compare that to for your visual cues.

[#3]

Thanks for the response, can you do the sum target within that set analysis? I need it to take a sum of all the targets for that name rather than just look at the target field if that''s possible?

In my main file you see I am having to take a sum of a weekly target from a different table to make the overall target for the year, I just put it in as target in the example

As for what I want to do with the average, I want to take that and then say, Bill has 3 accounts is that 3 20% above or below the average (7/3) and if it is change the background colour to show it, if that makes sense? sorry quite new to QV thanks for the help!

[#4]

You should be able to do what you're asking, but I'm not following exactly what you are asking. First 7/3 = 233%. Do you want 3/7, which would be a representation of the percent of Targets for each person?

Can you give me the values you are looking for? The last expression I gave will give you 3/7 for bill. I don't understand where the 20% comes from and what the average is that you want to compare to.

Also, what are the Targets? Sum(Target) is giving you big values for each person. Do you want the number of Targets for each person compared to the total number of Targets for everyone?

[#5]

please see new file attached which is more like how targets are calculated in my main app (sorry about that I just made it that way originally for simplicity)

ok so first the 7/3 bit what I am saying is there are 3 names in that list with 7 accounts between then so 7/3 = 2.33 so I am saying the average number of accounts for those names is 2.33 accounts.

From that 2.33 I then want to evaluate each persons number of accounts i.e Bill's 3 accounts and see if his 3 is 20% above or below the average of 2.33 and flag up that number 3 with a new background colour if it is, does that make sense? sorry not explaining this very well!

The sum of Targets is just being used to limit the data and show only those names and the number of accounts for those names, where there is a target for that name.

Thanks

[#6]

Ok, now I follow. I was confusing the 3 names with 3 targets for Bill. That's also why the 7/3 didn't make sense to me.

I still think you need to change the first expression to Set Analysis. You will notice that the total on your first expression says 10, when the total is really 7. If you change that first expression to:

Count({<[Target(weekly)] = {'>1'}>} DISTINCT [Account ID])

The total will correctly give you 7. Then you can use this to get that 7 within those records:

Count({<[Target(weekly)] = {'>1'}>} DISTINCT TOTAL [Account ID])

Once you have that 7, you just need to count the number of users to get the 3. I haven't figured that part out yet. I'm going to take a look and see if I can come up with something and try to get the visual cues as well.

EDIT: Ok, some more info from the attached sample. The number of names can be calculated using:

Count({<[Target(weekly)] = {'>1'}>} DISTINCT TOTAL Name)

Then I made a variable called vAvg to store the 2.33. The value of the variable should be:

=Count({<[Target(weekly)] = {'>1'}>} DISTINCT TOTAL [Account ID])
/
Count({<[Target(weekly)] = {'>1'}>} DISTINCT TOTAL Name)

Then instead of using Visual Cues, I used the Expression detail to change the background color. My example isn't the Background Color expression you wanted, but I wasn't sure exactly how you wanted to apply to +-20%.

Take a look at that and let me know if you need some clarification.

[#7]

ah thanks yea I see what you are doing makes sense to me.

Now for the hard part to see if this will work in my main app, it's alot more complicated with link and fact tables in it so crossing my fingers and will let you know how it goes

EDIT:

hmm nope it seems my main app doesn't like using that set analysis (returning no values), I have attached a layout of my table structure I would assume the fact/link table is messing with that set analysis?

When I use just the Target(Weekly) in the set analysis I get no value, but I know that if i use a sum of Target(Weekly) it would, is there any way to make the set analysis work form the sum of Target(Weekly) instead?

[#8 · Accepted solution]

Are you saying that instead of:

Count({<[Target(weekly)] = {'>1'}>} DISTINCT [Account ID])

You want:

Count({<[Sum(Target(weekly))] = {'>1'}>} DISTINCT [Account ID])

That won't work, because Set Analysis requires a field, not an expression for the Field Selection. Looking over those expressions again, I don't see why that logic would not work for what you are looking for. I just don't know enough about your dataset to tell you how to fix it.

From your sample app, it looks like some of the Targets have values and some have nulls. Essentially, you are trying to count non-null Targets. Is that correct? In that case, you don't even need the >1 Set Modifier, this should work:

Count({<[Target (Weekly)] = {'*'}>} DISTINCT [SLX Account ID])

Does that return anything in your dataset? I modified some of the field names to match what I saw on your table. You should be able to use Set Analysis, because all your tables appear to be linked (it's hard to tell from the image). It's just a matter of getting the right syntax based on your data.

[#9]

ah okay it's not possible to use set analysis like that then I see, I didn't design the app so my working knowldege of the links etc isn't 100% but yes all the tables are linked but I think the links are done in such a way in the Targets table that it doesn't like using the set analysis in there, i tried

Count({<[Target (Weekly)] = {'*'}>} DISTINCT [SLX Account ID])

but got nothing back, so what I think I will try is to create a new field in the relationship table which is basically just a 'yes' flag based on the

targets table and link that during the load so I can use it as the field for the set analysis. Will let you know how it goes wish me luck!

Thanks for all the help

[#10]

Welll I am pleased to report pulling in that new field and working the set analysis from that has worked a dream! thanks for all the help again, now time to go about replacing all the tables and variables that use it fun fun