7 Replies Latest reply on Sep 18, 2017 2:40 PM by Jim Dehner

do not affect the avg line due to the category

Deniz Atcali Sep 17, 2017 3:13 PM

Hi,

I have a calculated field to calculate the satisfaction of the clients from the results of a survey and it is like that :

Satisfaction :

SUM(IF [Result] = '2' THEN

0.2 * [Number of Records]

ELSEIF [Result] = '3' THEN

0.5* [Number of Records]

ELSEIF [Result] = '4' THEN

0.8* [Number of Records]

ELSEIF [Result] = '5' THEN

1* [Number of Records]

END)

/ SUM([Number of Records])

This is visualized as below changing by a category filter in the sheet :

And I want the avg line to be stable when i change the category on the right. i tried to use LODs but couldn't fix it. as i am new to the tableau I will appreciate all of your help. thanks.

Deniz

1. Re: do not affect the avg line due to the category

Jim Dehner Sep 16, 2017 6:34 AM (in response to Deniz Atcali)
Good morning
I can't say that I understand your calcualtions but the formula below will give you a fixed line -

{ FIXED
SUM(IF [Result] = 2 THEN
0.2 * [Number of Records]
ELSEIF [Result] = 3 THEN
0.5* [Number of Records]
ELSEIF [Result] = 4 THEN
0.8* [Number of Records]
ELSEIF [Result] = 5 THEN
1* [Number of Records]
END)
/ SUM([Number of Records])
)}

the result looks like this

I just put the formula on a refence line so that it went across the entire viz

Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

satisfaction.twbx 21.6 MB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: do not affect the avg line due to the category

Deniz Atcali Sep 17, 2017 12:46 AM (in response to Jim Dehner)

Thanks Jim for your helpful reply,

let's just call the one you suggested the second avg line, I just did and visualzied the two avg lines, the second really didin't change while i change the categroy filter, just an another question, the two avg lines aren't exactly the same, very very close but not the same, do you have an idea?
and i also added a month filter the the viz and the second avg line also didn't change and i want it nit to change only due to the category filter.

thanks again.
Deniz
Like Show 0 Likes(0)

Actions
3. Re: do not affect the avg line due to the category

Jim Dehner Sep 17, 2017 6:31 AM (in response to Deniz Atcali)

Thanks - My "Second Sverage Line" is not using the "Average " from the analytics - it is using a reference line that I set to the calculated average based on the Fixed calcualtion - I am not certain what your Case statement and "averaging" will do with Null values in both the weighting calcualtion and in the sum records portion -
Jim
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
4. Re: do not affect the avg line due to the category

Deniz Atcali Sep 17, 2017 3:16 PM (in response to Jim Dehner)
Hi again Jim, I got your point, in fact what you advised me served me a lot and i got your point about null values. thus i tried to have a twbx file closest to my real work and i eliminated all the null values. as you can see in the pic below, i have a satisfaction (weighted avg of my own) and the fixed one ({ FIXED : [Satisfaction]}), and i have no problem while i change the category, but when i deselect all in the moths filter, and even choose one month the two avg points are different, in fact i only wanted that my fixed expression would fix only the category but now i couldn't understand what it fixes and how does it calculate this value. perhaps the attached twbx file would say a lot if you have time to look at it.

Thanks a lot.

satisfactionFixed.twbx 10.5 MB
Like Show 0 Likes(0)

Actions
5. Re: do not affect the avg line due to the category

Jim Dehner Sep 18, 2017 6:53 AM (in response to Deniz Atcali)
Hi
I am finally back from travelling and took a hard llok at this - the way a fixed LOD expression work is it creates permutaions of all dimensions that appera befor the colon (:) and then aggregates the measures based on the forumla after the colon - so if I understand correctly you want to know know for each month (FIXED) what the Satisfaction is for all categories and be able to vary the category and see how the selected category compares to the monthly total - I mis-undertood before - thought you wanted the fixed total across all months -

so what you want to do is fix at the month level -see below - I recacluated the function

{ FIXED MONTH([Add Time]):

SUM(IF [Result] = '1' THEN 0* [Number of Records]
ELSEIF [Result] = '2' THEN
0.2 * [Number of Records]
ELSEIF [Result] = '3' THEN
0.5* [Number of Records]
ELSEIF [Result] = '4' THEN
0.8* [Number of Records]
ELSEIF [Result] = '5' THEN
1* [Number of Records]
END)
/sum([Number of Records])}

Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

satisfactionFixed.twbx 10.8 MB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
6. Re: do not affect the avg line due to the category

Deniz Atcali Sep 18, 2017 8:23 AM (in response to Jim Dehner)

Dear Jim, you are great, and your explanation is very helpful, thanks a lot!!!
Like Show 0 Likes(0)

Actions
7. Re: do not affect the avg line due to the category

Jim Dehner Sep 18, 2017 2:40 PM (in response to Deniz Atcali)

you are welcome
Glad to help out
Jim
Like Show 0 Likes(0)

Actions